Linked tables, automatically expanding

[vLeeuwen]

Hi All,

First post here. I've a little question that is giving me a bit of a headache. Is it possible to link to tables in excel, so that if I add a entry (a new row) to one of the tables the other one will automatically expand with a new row?

Thanks!

 

[Jon von der Heyden]

I take you are talking about table objects (formerly known as list objects)?

Not without VBA. If you want VBA suggestion please tell us where the tables are (sheet names and ranges); and which field drives the change.

 

[vLeeuwen]

Yes I'm talking about table objects. I already guessed it would come down to VBA, but my VBA knowledge is far from sufficient.

I've got the table Input on a sheet called Data, this table has a range [B4:F11] (without headers). The other table is called Calculation and is on the sheet Lists and has a range [G4:I11] (also without headers). The new line will be inserted at the Input table.

Thanks!

 

[ExcelHopeful]

I am having this same issue. I have 3 tables: Table1, Table2, and Table3. In Table3 there are 3 columns: Suppliers, Aluminum, and Steel. I want to make it so that Table2 autmatically adjusts and pulls the Supplier name from Table1/column1 when there is n entry in for Aluminum. Table2 will do the same thing, but it will only pull suppliers when there is an entry for Steel. Thanks!

 

[ExcelHopeful]

Btw, I've somewhat completed what I've asked above. The problem is that you have to make sure your tables are manually expanded first. However, ounce you do this then it will pull the data from the other tables.

The first equation I used to pull suppliers that had steel is

=IFERROR(INDEX('Table1'!$A$3:$A$622,SMALL(IF('Table1'!$N$3:$N$622<>"",ROW('Table1'!$A$3:$A$622)-ROW('Table1'!$A$3)+1),ROWS('Table1'!$A$3:$A3))),"")

The second equation I used to pull suppliers that had aluminum is

=IFERROR(INDEX('Table1'!$A$3:$A$622,SMALL(IF('Table1'!$K$3:$K$622<>"",ROW('Table1'!$A$3:$A$622)-ROW('Table1'!$A$3)+1),ROWS('Table1'!$A$3:$A3))),"")