Excel help - auto-generating table rows from another table
December 6, 2021 1:46 PM Subscribe
I need help in understanding how (if possible) to get Excel to update an existing table to have the same number of rows as another table.
Short version: if I insert a new row in Table A I want to have that same row inserted in Table B.
Imagine Table A (called Rawdata) is a template for a .csv dump of a supplier's products. It has all the information arranged appropriately - one item per row, dozens of columns of product name and size and cost and retail price and whatnot. At the far right are few columns of formulas to give information that's not readily apparent in the data dump, like say a case price divided by the pack size to generate a per-unit price.
Imagine Table B (called Pretty) is a simplified version of Rawdata. It has three columns like item name, size, and unit price. This is the worksheet that management sees and that will get printed and passed around.
I know how to save a template file for myself for the Rawdata table so I can just paste the CSV output into it and because it's a table it will generate the appropriate number of rows and columns to fit, it'll copy down all the formulas, etc.
Pretty, however ... how can I leave my template with Pretty having one or two rows (column header and a reference to Rawdata row 1) and then have Pretty expand down to the same number of rows as Rawdata when I paste in that .csv?
If it weren't for the printing I'd just do a bunch of =ISBLANKs or something, but one supplier may only have 20 items and another has 500, and if management goes to print the Pretty for the 20-item supplier they're going to have a ton of seemingly blank pages because they wouldn't check to see at which page number to stop.
I feel like I'm making this more complex than it should be but I can't figure out how to do what I want.
Because of Reasons the answer of "well just paste the data in Rawdata and then delete the columns you don't want" isn't a solution at this time. I really need all of that information there, I just need a second worksheet that shows people only the information they want (and with all the conditional formatting and colors and whatnot).
Short version: if I insert a new row in Table A I want to have that same row inserted in Table B.
Imagine Table A (called Rawdata) is a template for a .csv dump of a supplier's products. It has all the information arranged appropriately - one item per row, dozens of columns of product name and size and cost and retail price and whatnot. At the far right are few columns of formulas to give information that's not readily apparent in the data dump, like say a case price divided by the pack size to generate a per-unit price.
Imagine Table B (called Pretty) is a simplified version of Rawdata. It has three columns like item name, size, and unit price. This is the worksheet that management sees and that will get printed and passed around.
I know how to save a template file for myself for the Rawdata table so I can just paste the CSV output into it and because it's a table it will generate the appropriate number of rows and columns to fit, it'll copy down all the formulas, etc.
Pretty, however ... how can I leave my template with Pretty having one or two rows (column header and a reference to Rawdata row 1) and then have Pretty expand down to the same number of rows as Rawdata when I paste in that .csv?
If it weren't for the printing I'd just do a bunch of =ISBLANKs or something, but one supplier may only have 20 items and another has 500, and if management goes to print the Pretty for the 20-item supplier they're going to have a ton of seemingly blank pages because they wouldn't check to see at which page number to stop.
I feel like I'm making this more complex than it should be but I can't figure out how to do what I want.
Because of Reasons the answer of "well just paste the data in Rawdata and then delete the columns you don't want" isn't a solution at this time. I really need all of that information there, I just need a second worksheet that shows people only the information they want (and with all the conditional formatting and colors and whatnot).
Power Query would allow you to do it.
But, there could be a simple solution as well, if I understood you correctly. Define "Names" for each column, and use those names in formulas, instead of cell references, in your Pretty sheet.
To define the names, select the columns you are using in your Raw Data (don't just select existing cells, select the whole columns by clicking on A through the last column). Then go to "Formulas" ribbon, and select "Create from Selection". Assuming you have labels in the first row, leave "Top Row" as the only checked box in the next dialog box. Now each row has a name defined.
You can now use those names in the Pretty sheet, with one modification (because the full column is defined as a name, it will try to put '0' instead of Null in the empty fields, so you may have to write an If statement to replace those).
Here is a sample excel file to show you how it works. Let me know if you need more info.
posted by tuxster at 2:23 PM on December 6, 2021 [1 favorite]
But, there could be a simple solution as well, if I understood you correctly. Define "Names" for each column, and use those names in formulas, instead of cell references, in your Pretty sheet.
To define the names, select the columns you are using in your Raw Data (don't just select existing cells, select the whole columns by clicking on A through the last column). Then go to "Formulas" ribbon, and select "Create from Selection". Assuming you have labels in the first row, leave "Top Row" as the only checked box in the next dialog box. Now each row has a name defined.
You can now use those names in the Pretty sheet, with one modification (because the full column is defined as a name, it will try to put '0' instead of Null in the empty fields, so you may have to write an If statement to replace those).
Here is a sample excel file to show you how it works. Let me know if you need more info.
posted by tuxster at 2:23 PM on December 6, 2021 [1 favorite]
If you're using Excel 365 (or any version with Dynamic Arrays), you can use FILTER. Here are two references:
https://www.ablebits.com/office-addins-blog/2020/06/17/excel-filter-function/
https://stackoverflow.com/questions/69577167/excel-filter-function-choose-certain-columns-as-output
I got the StackOverflow one to work as you're describing.
posted by Gorgik at 7:50 PM on December 6, 2021
https://www.ablebits.com/office-addins-blog/2020/06/17/excel-filter-function/
https://stackoverflow.com/questions/69577167/excel-filter-function-choose-certain-columns-as-output
I got the StackOverflow one to work as you're describing.
posted by Gorgik at 7:50 PM on December 6, 2021
In google sheets there's something called an Array formula or IMPORTRANGE.
Either of those on google sheets should do the trick. And, just google corresponding formulas on Excel.
posted by iNfo.Pump at 9:00 PM on December 6, 2021
Either of those on google sheets should do the trick. And, just google corresponding formulas on Excel.
posted by iNfo.Pump at 9:00 PM on December 6, 2021
I think if you generate a Pivot Table from your Raw data it will do the trick? You can filter which rows and columns to show. And if you select "Show in tabular form" for all your column items you will essentially generate a new filtered table.
If you convert your raw data table into a named? table (Insert -> Table) it will update automatically.
posted by SweetLiesOfBokonon at 5:30 AM on December 7, 2021
If you convert your raw data table into a named? table (Insert -> Table) it will update automatically.
posted by SweetLiesOfBokonon at 5:30 AM on December 7, 2021
I think I get what the question is. I do this, and I've solved it by the code behind "Pretty" being e.g. A1 = 'Messy!A1. Then you just need to define the top row reference for each pretty column. If you autofill down in Pretty way below what you need, you'll see 0 input for everything else. You could conditional format anything that's a 0 as white text if it needs to be ready for outputting directly.
posted by london explorer girl at 6:58 AM on December 7, 2021
posted by london explorer girl at 6:58 AM on December 7, 2021
Response by poster: Hi all, thanks for your input. I should have thought to add that the organization is still on Excel 2010 and therefore none of us have PowerQuery by default. I could install an older version of it on my machine but the main goal is to free me up from running this report and then manually changing the data - I want management to be able to run the report, paste the .csv into Rawdata, and have their report.
To that end, the solutions offered by some of you work for the screen, but they require formulas down far enough that they're there to capture every potential row of the .csv. I was hoping to avoid that, because if one clicks print then Excel generates a page for anything that has data in it. I think tuxter's example file was something like 23,000 pages. I can't count on management to look at a print preview and see how many pages to print and custom define the start and end pages.
So what I was looking for was a solution that would only generate the formulas as far down Pretty as the data went down in Rawdata, and what I'm seeing from the answers here is that there's not a default way to do this, at least not in Old Excel.
However! I think SweetLiesOfBokonon may have my best solution. Though I do pivot tables every single day I had never laid one out in tabular form as suggested. If I remove the expand/collapse buttons I think I might be getting where I want to be. Have to go experiment with this. Telling folks "just right-click here and choose 'refresh'" is simple enough. Thank you!
posted by komara at 1:15 PM on December 7, 2021
To that end, the solutions offered by some of you work for the screen, but they require formulas down far enough that they're there to capture every potential row of the .csv. I was hoping to avoid that, because if one clicks print then Excel generates a page for anything that has data in it. I think tuxter's example file was something like 23,000 pages. I can't count on management to look at a print preview and see how many pages to print and custom define the start and end pages.
So what I was looking for was a solution that would only generate the formulas as far down Pretty as the data went down in Rawdata, and what I'm seeing from the answers here is that there's not a default way to do this, at least not in Old Excel.
However! I think SweetLiesOfBokonon may have my best solution. Though I do pivot tables every single day I had never laid one out in tabular form as suggested. If I remove the expand/collapse buttons I think I might be getting where I want to be. Have to go experiment with this. Telling folks "just right-click here and choose 'refresh'" is simple enough. Thank you!
posted by komara at 1:15 PM on December 7, 2021
Pivot tables may not work if there is no data aggregation.
What about adding a helper column in column A. Call column A, "is blank". Then filter column A on False. Then minimize or hide column A.
Filtering only works on rows, not columns though. You may also be interested in Form Controls or ActiveX Controls.
posted by oceano at 3:53 PM on December 7, 2021
What about adding a helper column in column A. Call column A, "is blank". Then filter column A on False. Then minimize or hide column A.
Filtering only works on rows, not columns though. You may also be interested in Form Controls or ActiveX Controls.
posted by oceano at 3:53 PM on December 7, 2021
komara, sorry I had not paid attention to the printing requirements (last time I printed something was probably 5 years ago, our office is now all electronic).
Here is a new solution, but I don't know if it works the same in Excel 2010 (I have Excel 365, and it works without any problem): If you use "Format as Table" for your RawData, it automatically names the columns (but only up to the end of your data) and you can use those names in the formulas in the first rows of your Pretty sheet.
Even better, the table on RawData automatically extends and only to the end of the data that you paste, so when you paste new data in it, it will actually only extend the data on Pretty sheet up to the end of the data, solving your printing problem.
To do this, select the data on RawData, select "Format as Table" under the Styles section of the Home ribbon (might not be in the same place in Excel 2010). Here is a sample spreadsheet.
posted by tuxster at 11:37 AM on December 8, 2021
Here is a new solution, but I don't know if it works the same in Excel 2010 (I have Excel 365, and it works without any problem): If you use "Format as Table" for your RawData, it automatically names the columns (but only up to the end of your data) and you can use those names in the formulas in the first rows of your Pretty sheet.
Even better, the table on RawData automatically extends and only to the end of the data that you paste, so when you paste new data in it, it will actually only extend the data on Pretty sheet up to the end of the data, solving your printing problem.
To do this, select the data on RawData, select "Format as Table" under the Styles section of the Home ribbon (might not be in the same place in Excel 2010). Here is a sample spreadsheet.
posted by tuxster at 11:37 AM on December 8, 2021
Response by poster: Thanks again for your help, tuxter and everyone else. It appears that because of Other Reasons I have to abandon doing it this way anyway.
posted by komara at 1:15 PM on December 13, 2021
posted by komara at 1:15 PM on December 13, 2021
« Older How can I get people to stop partying on my... | With an up-to-date firewall, do the OSs behind the... Newer »
This thread is closed to new comments.
posted by oceano at 2:00 PM on December 6, 2021 [1 favorite]