Excel help - auto-generating table rows from another table

Post date: 2021-12-07 10:46:06

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).

Please click Here to read the full story.