Dynamically extending or de-extending a data table based on another table

**Seraph122** · 11-02-2012, 04:01 PM

Hi Everyone,

I'm trying to have a table add an additional line item based on another table in another sheets' entries. I've attached a file that represents the situations. I have 3 tables, each on a separate tab, and I have a summary tab that has another table that has lists of numbers. I want the number of rows in the corresponding tables to line up with the entries in the summary tab. I want the first column of each table to correspond to the summary tabs' values.

Can someone assist me with this? I would greatly appreciate it.

Thanks!!

**FDibbins** · 11-02-2012, 04:51 PM

not sure if this is what you want, but try this in sheet2 B5...
=INDEX(Table1[#All],MATCH(Table2[[#This Row],[Column1]],Table1[[#All],[Column1]],0),MATCH(Table2[[#Headers],[Column2]],Table1[#Headers],0))

it can also be used on sheet3

**Seraph122** · 11-02-2012, 06:03 PM

Originally Posted by FDibbins

not sure if this is what you want, but try this in sheet2 B5...
=INDEX(Table1[#All],MATCH(Table2[[#This Row],[Column1]],Table1[[#All],[Column1]],0),MATCH(Table2[[#Headers],[Column2]],Table1[#Headers],0))

it can also be used on sheet3

Thanks for the quick reply!

Sorry I might have been unclear, I tried the solution and it didn't seem to work, so I'll try and re-explain the situation. In the summary tab, there are 3 columns corresponding to the 3 tables across 3 tabs. So for example, if I add another entry after "29" in the Table 1 column, I would like that entry to appear as another row. So if I enter 45 after 29 under the Table 1 column in the Summary tab, i would like a new row to be created in Table 1, with 45 under 29. Let me know if that makes sense.

Thank you again for your assistance, it is very much appreciated.

**FDibbins** · 11-02-2012, 08:53 PM

ok i think i understand now, and the best I can offer is, 1 line above the last line on each sheet, add a hole bunch of new rows (as many as you think you may need?) then, in place of the formula you have in column A, use this instead...

=IF(Summary!C33="","",Summary!C33)

Repeat this for the other sheets

sorry, without using VBA (my weak point), thats the best i can do

**Seraph122** · 11-04-2012, 06:25 PM

Hi FDibbins,

I thought about doing that before, but the problem is that the tables have other columns that feed off of that first column, so I would have to go and change all of those columns to hide the errors - which is okay, but quite troublesome. I also think having blank rows may cause issues down the line.

Thanks for your help, if you or anyone else comes up with another suggestion I would greatly appreciate it!

As a side note, I'm really trying to avoid VBA and macros because this will be a report that is sent out to multiple people, who may or may not have macros enabled.