|
Is it possible to use vba to import a row from one sheet to another just by typing a specific number in a cell? For example: I want to be able to type the number 150 or a text string like ABCD in a cell in Column A, and have Excel search a specific column in a different sheet for that string and then copy the contents of the row containing that string to the current row I'm typing in. I've tried googling this and I can't seem to find anything like this, or any commands that seem like they can do exactly this.
|
# ? Jan 18, 2023 11:26 |
|
|
# ? May 13, 2024 05:16 |
|
You should be able to use the row number or column letter as an indexer on the row/column collection object property that a workbook has to access the specific row/column and then simply assign it to the row/column in the other workbook using the assignment operator. Or a for loop to iterate through the cells of the row column to copy each one individually In this example, everything should be self explanitory. sourceSheet is just the name of the sheet you're copying from. destinationSheet where you're copying to. destinationRow is the place you want your copied row to go. If you want to put it in the same place as the original sheet, change it to sourceRow sourceRow is where you're copying from. as long as the user types a number in the cell you're looking at it can be as simple as: code:
Iterating over that row to copy it will look like this: code:
HootTheOwl fucked around with this message at 14:40 on Jan 18, 2023 |
# ? Jan 18, 2023 13:17 |
|
I have a sheet of data, which I am able to filter(unique()) on to get a list of names. I want each of these names to be repeated 3 times in the output, so e.g. if my formula is in A2 I want Data!A2 in A2, A3, A4; Data!A3 in A5, A6, A7, and so on. Is there any way to do this without macros/VBA? e: Just putting the output in A2, A5, A8, and so on is also "good enough" for what I want this for.
|
# ? Jan 24, 2023 20:44 |
|
Xerol posted:I have a sheet of data, which I am able to filter(unique()) on to get a list of names. I want each of these names to be repeated 3 times in the output, so e.g. if my formula is in A2 I want Data!A2 in A2, A3, A4; Data!A3 in A5, A6, A7, and so on. Is there any way to do this without macros/VBA? I asked chatgpt exactly what you asked, and it returned this =INDEX(Data!A:A, ROUNDUP(ROW()/3,0)) which does work. drat that tech is crazy. quote:I have a sheet of data, which I am able to filter(unique()) on to get a list of names. I want each of these names to be repeated 3 times in the output, so e.g. if my formula is in A2 I want Data!A2 in A2, A3, A4; Data!A3 in A5, A6, A7, and so on. Is there any way to do this with a formula? quote:Yes, you can use the INDEX and ROW functions in combination to repeat each name from your list 3 times in the output. Here is an example formula you can use:
|
# ? Jan 24, 2023 21:49 |
|
Small Excel Questions Not Worth Being Answered by a Human
|
# ? Jan 24, 2023 22:52 |
|
Lib and let die posted:Small Excel Questions Not Worth Being Answered by a Human It's crazy how good it is, like just trying AG3's question gives working results. quote:Yes, it is possible to use VBA to accomplish this task. You can use the "Find" method to search for the specific number or text string in the specified column of the other sheet, and then use the "EntireRow" property to copy the contents of the entire row containing that string to the current row. Here is an example of how you can accomplish this using VBA: Of course, this one would need a little edit to see the other workbook instead of looking up the current one, but the code does work and is a good starting point. Here is what I get by editing the quesiton a bit quote:Sub ImportRow() you could then use a shortcut and press that to bring the data over. Harminoff fucked around with this message at 23:11 on Jan 24, 2023 |
# ? Jan 24, 2023 23:08 |
|
Chat GPT can read the MSDN just as good as anyone. Except me, of course. I'm still worth the big bucks.
|
# ? Jan 25, 2023 00:07 |
|
Ask ChatGPT how to generate a Data Validation list based on the value of another cell.
|
# ? Jan 25, 2023 01:07 |
|
That’s pretty neat but I’m not scared for my job as an “Excel guy” just yet, I work with people who don’t even realise you can just Google these questions so the chances of them asking gpt are pretty slim.
|
# ? Jan 26, 2023 04:04 |
|
I'm kind of surprised to be struggling with this as it seems like a fairly straightforward thing, but none of the tutorials online seem to have an answer. I have a list of expenses like this: code:
code:
code:
|
# ? Jan 31, 2023 03:24 |
|
Wouldn't you literally just create a PivotTable with "Vendor" under "rows" and "Amount" under "values" with display as sum? Unless I am misunderstanding the question
|
# ? Jan 31, 2023 03:40 |
|
KOTEX GOD OF BLOOD posted:I'm kind of surprised to be struggling with this as it seems like a fairly straightforward thing, but none of the tutorials online seem to have an answer. prefix any range or cell in the second worksheet with the name of the name of the first worksheet plus an exclimation point. So if you want column A from sheet 1 (named source): code:
|
# ? Jan 31, 2023 14:42 |
|
Toe Rag posted:Wouldn't you literally just create a PivotTable with "Vendor" under "rows" and "Amount" under "values" with display as sum? Unless I am misunderstanding the question Thanks!
|
# ? Feb 1, 2023 04:04 |
|
I hope a Google Sheet question is okay here I have a column in a Google Sheet where I copy and paste a date in YYYY-MM-DD format from the data we receive from the supplier. However, since we have many suppliers and thousands of rows of data, sometimes the date format they send to us is incorrect which messes up the automated data extraction. What would be the best way to ensure that when the data is copy and pasted to have some sort of notification that the provided date format is incorrect? I was looking into conditional formatting but not sure if that would suffice.
|
# ? Feb 6, 2023 09:42 |
|
Busy Bee posted:I hope a Google Sheet question is okay here If you're talking about where one field has the date as "2/2/2023" and another one has "Feb 2, 2024" and you want it formatted to YYYY-MM-DD. Click on the Column Header that has the Date field (so if it is Column B, click on the B in the sheet so that it highlights the entire column), After, go to Format|Number and choose "Custom date and time formats." By default it should already be using YYYY-MM-DD so just click Apply. If you want to highlight invalid columns to show ones that Google could not format: Go into your sheet, Click on Format|Conditional Formatting, and click on "Add another rule" Under, "Apply to Range:" Specify, A1:<X>, where <X> is the last column in your sheet. If you have headers, use A2:<X> So if you have 5 columns, where the last one ends in E, you would input "A1:E". If you have headers it would be, "A2:E" Under Format Rules: Under, "Format cells if...", Choose "Custom Formula is" at the very bottom. In the Input Box underneath where it now says, "Custom Formula is" put in code:
Choose a color in the Formatting Style (click on paint bucket or click on the header to choose Green/Yellow/Red This will now highlight any rows that Google deems to be an "invalid" date. ------
|
# ? Feb 6, 2023 10:17 |
|
Harminoff posted:It's crazy how good it is, like just trying AG3's question gives working results. Hey, after reading this I tried it myself, and I guess I phrased it differently because it gave me this code: code:
|
# ? Feb 15, 2023 13:17 |
|
Does this do what you are looking for? Put it in sheet1 instead of its own module. I have two sheets, one called sheet1, and the other called data. Data has things like 123|tom|bob|roger in sheet1, if I type 123 into cell a1, it will then become 123|tom|bob|roger code:
|
# ? Feb 15, 2023 16:57 |
|
It does, thank you very much!
|
# ? Feb 17, 2023 14:39 |
|
Hi all. After a full day of searching and looking and watching I've finally thrown up my hands and decided to ask for help. I've been tasked by my boss to see if I can come up with a spreadsheet that helps track maintenance for boat engine components. Each component needs to be changed or maintained after a certain threshold of use-hours, or elapsed time, whichever comes first. I've got a basic idea of how the sheet should be laid out, as such: As you can see, I'm using Google Sheets. I've managed to figure out a few things on my own (I am a strict novice at programming). The idea is that the user inputs a boat's current use-hours and the date it was measured (under the boat name). Upon entry of the date the spreadsheet fires a script that stores the input hours and date as variables, then writes those to the last recorded hours field(s). The difference in use-hours and dates is then stored as well. I want to then use that difference and add it to each individual component's use-hours or elapsed time fields. So, as an example with the above image, if I record the current hours as 40 and the date as the 20th of Feb, the script would then update all fields under each engine on that boat. The Engine Oil field would then show 50 (hours) and 22 (days). The problem is that I can not for the life of me figure out how to use a script to add that script's variable's value to the current values of a range of cells (efficiently). I was playing around and managed to kinda make it work by making a bazillion variables, writing those to a separate sheet, and then using the setFormula function, but that was really really slow, both in the programming but also in the computation time. I know there's gotta be an easier way, I just don't know what I'm looking for. Part of the reason I'm having so much difficulty is that a cell's formula field can't self-reference its current value and then add a script's variable to then spit out a new number (script variables are not global, so the spreadsheet doesn't know what its referencing). Instead I have to store each cell's current value as a variable in the script, add it to other variables, then output that new value into a sheet's cell so I can then use a formula function on the target cell. It's all very convoluted and time-consuming. I suspect I'd need to pull the data range by odd/even rows, and then put it into an array. I'd then amend the array by the variable value and return to the sheet. I just have no idea how to do that. Or if that's even the best solution. Most of what I've found on Google and Youtube isn't helpful in that regard. I hope what I've written makes sense. If any of you can tell me how to do it better or just point me in the right direction code-wise I'm sure I will eventually figure it out.
|
# ? Feb 18, 2023 14:26 |
|
I'm having trouble understanding what you want but a quick and dirty way to get around circular references is nice inputs to a different cell or worksheet all together.
|
# ? Feb 19, 2023 03:50 |
|
Trivia posted:I hope what I've written makes sense. If any of you can tell me how to do it better or just point me in the right direction code-wise I'm sure I will eventually figure it out. A couple of suggestions:
Given your template, this is how I would approach it. I would define an array that contains the areas I am interested in for each boat. This gives you flexibility if you have boats with different numbers of engines or items, or you just want to change the sheet formatting. Then when a cell is updated, check to see if it's a current date cell for one of the boats and update that boat by reading and then writing all of its values at once. JavaScript code:
|
# ? Feb 19, 2023 07:39 |
|
Thanks for the help all, and sorry for my lovely technical writing lol I'll pore over Ninja.Bob's post and see if I can't figure out what the hell it means.
|
# ? Feb 20, 2023 15:47 |
|
Is there any uglier language than DAX? Survey says: no.
|
# ? Mar 13, 2023 20:15 |
|
I’ve just been using DAX for a couple of weeks in power bi and it’s ain’t fun. I started with calculated columns trying to do things, but I ended up with a single measure that does the currency conversion and displays the right segments using selecteditem() values. It works but it was a hell of a journey to get there.
|
# ? Mar 14, 2023 01:35 |
|
DRINK ME posted:I’ve just been using DAX for a couple of weeks in power bi and it’s ain’t fun. I have zero affiliation with these guys but I've found their free "Intro to Dax" course to be pretty approachable: https://www.sqlbi.com/training/ I always found an online DAX repl which I've been poking about with.
|
# ? Mar 14, 2023 03:19 |
|
Thanks We have a bunch of free internal training on every topic through some subscription service so I’d probably have some dax stuff in there as well. I’ve just been stealing stuff from the powerbi forums and stackexchange to get it to work. Reminded me that I need to quickly run through either a Power Apps or Power Automate training before the end of month to meet my learning KPI for the year - can’t recall which it was because the bosses boss chose that for me, 8 months into the financial year, and I haven’t looked at it yet.
|
# ? Mar 14, 2023 03:39 |
|
is there conditional formatting formula that allows for - highlighting odd cells (i.e. ignore every other row) - only if those cell values are less than 2 at the same time? I can't find a way to combine ODD(ROW())=ROW() with a logical function that checks the cell contents for the value > 2
|
# ? Mar 20, 2023 12:39 |
|
double nine posted:is there conditional formatting formula that allows for Use modulo division on the row number: code:
|
# ? Mar 20, 2023 12:42 |
|
double nine posted:is there conditional formatting formula that allows for For example from row E4: = And( modulo( Row(E4) , 2 ) , E4 > 2 )
|
# ? Mar 20, 2023 12:50 |
|
ty
|
# ? Mar 20, 2023 13:00 |
|
This is a pivot table question. I'd be happy to have this answered from an Excel perspective and figure out how to reproduce it i Sheets/Looker at a later date. Copy and pasting my question from Reddit: I work in a call centre. I'm trying to create a dashboard in Google Looker for displaying agent quality performance. I'm having trouble getting pivot table to do what I want it to do, and also have a follow up question about drill downs (but knowing that this is the Google Sheets sub, i'll focus my question on the pivot table). Here is my framework: -Agents get given a rating on a bunch of questions. These are 5 item scale (Bad, Average, Good, Very Good etc.) -Every evaluation that gets completed has it's own line on a report. It ends up looking something like this -There is correspondending commentary to justify the marking of each question, resulting in a column for each question and column for each questions commentary. Something like this -I created a summary that looks like this to quickly identify areas of improvement. To do this I created extra columns to give the answer to each question a numerical value eg. =IF(CELL="Bad","1"), Good=2, VeryGood=3 etc. Ideally, I'd like to be able to double click on a cell in that chart, for example the top left one being James Baker/Acknowledgment and it would drill down/go to another chart/table that shows a filtered view of all the comments for Acknowledgement that contributed to James Baker's rating. An alternative display is that I'd like a pivot table (or a 100% stacked bar chart) that displays the "Question" on the X-axis/columns and the "Rating" (good, bad etc.) as the Y-Axis/Row/Breakdown of the bar. But I don't know how to get "Rating" answers to display as the colums. I want it to look something like this in pivot table form or this in bar chart form from which i'd be able to double click any of those cells/colour blocks and it would take me to the commentary for that entry So in summary, my questions are: -With my data structured like it is, how do I create a pivot table to show "Question" as the column, "Rating" as the row and number of times that rating was given for each question as the data? -How do I create a bar chart to do the same? -Is it possible to attribute a numberic value to a text string so I can avoid the "create additional columns and lookup the value of another column to give it a number" step in the process? -Do i have the abiility to drill down to see the commentary for the associated question ratings? Hope this is clear, any help would be appreciated
|
# ? Apr 28, 2023 12:34 |
|
Looten Plunder posted:-Is it possible to attribute a numberic value to a text string so I can avoid the "create additional columns and lookup the value of another column to give it a number" step in the process? Do this first and then follow this page and you should be fine. https://superuser.com/questions/1084075/how-to-generate-an-excel-chart-that-includes-aggregating-and-grouping HootTheOwl fucked around with this message at 13:32 on Apr 28, 2023 |
# ? Apr 28, 2023 13:12 |
|
I would like to average all numbers where any of the following columns contain particular words, but my use of AVERAGEIF was giving me results that were way off (divide by zero errors, answers 1/4th expected values, et cetera). Is there a reasonable/rational way to match/average values based on the contents of multiple columns? Sample Data: code:
code:
|
# ? May 4, 2023 15:50 |
|
FreshFeesh posted:I would like to average all numbers where any of the following columns contain particular words, but my use of AVERAGEIF was giving me results that were way off (divide by zero errors, answers 1/4th expected values, et cetera). Is there a reasonable/rational way to match/average values based on the contents of multiple columns? In general, instead of AVERAGEIF you are probably better off doing a SUMIF divided by a COUNTIF. Does this solve your problem? It should only return a Div/0 error when you try to average a Result that doesn't exist in the data. There are other ways that are a little more scalable but this is probably simplest with what you have already built.
|
# ? May 4, 2023 16:28 |
|
FreshFeesh posted:I would like to average all numbers where any of the following columns contain particular words, but my use of AVERAGEIF was giving me results that were way off (divide by zero errors, answers 1/4th expected values, et cetera). Is there a reasonable/rational way to match/average values based on the contents of multiple columns? Your problem is that SUMIF doesn't like spanning across multiple columns for the if clause, and that SUMIFS only does the logical AND Thankfully this doesn't happen to Countif, it instead has a problem where you can't do "Countif(A:E)" so you havbe to provide bounds So the answer is: code:
|
# ? May 4, 2023 16:42 |
|
esquilax posted:In general, instead of AVERAGEIF you are probably better off doing a SUMIF divided by a COUNTIF. Does this solve your problem? It should only return a Div/0 error when you try to average a Result that doesn't exist in the data. Unfortunately SUMIF provides weird results too when I call it across a range of columns. In this example I would expect the answer to be 0, as the only line which contains the target text doesn't have a value in the sum field code:
|
# ? May 4, 2023 16:45 |
|
FreshFeesh posted:Unfortunately SUMIF provides weird results too when I call it across a range of columns. Oh, I was assuming you were already using a formula for each column on your AVERAGEIF functions the way that HootTheOwl is suggesting that you do. Yeah neither AVERAGEIF or SUMIF play nicely wth a criteria array that is a different size than the sum array.
|
# ? May 4, 2023 16:54 |
|
I genuinely appreciate the help, but as is so often the case I ran into another issue. The above approach (SUMIF/COUNTIF) works just fine for entries which contain all the data, but some rows lack the "Number" column (an example of which I should have included in my initial post), which means the averages [of entries with a value] get thrown way off. Because COUNTIFS has issue with ranges of differing sizes (e.g. I can't do a countifs for descriptor in columns B:D with a check for notblank in column A) I'm unable to make a simple tweak to the above formulae to make it work. My goal is to get an average of all cells that match the descriptor AND have a value in column A, if that makes sense.
|
# ? May 4, 2023 17:26 |
|
FreshFeesh posted:I genuinely appreciate the help, but as is so often the case I ran into another issue. You're going to need to do something you did for the sum of countifs, here: code:
HootTheOwl fucked around with this message at 17:58 on May 4, 2023 |
# ? May 4, 2023 17:56 |
|
|
# ? May 13, 2024 05:16 |
|
Could you concatenate the test results into one column and just run the averageif using text search criteria, like "*re-test*"?
|
# ? May 5, 2023 08:12 |