Formula to insert new row

[Jet Fusion]

Good morning all,

Is there a formula when adding a new row on sheet 1 it updates on sheet 2?

For example, if you see the test file I loaded, if we add a new row on the Datasheet the formula must let it then update on the Master sheet in the same place?

Thanks in advance
JetFusion

 

[Peter Bartholomew]

You are already using Tables. Take the next step and remove all direct cell references of the sort E5 and replace them by structured references.

One thing to be careful of is that the Master and Data Tables must be aligned by row. Otherwise the @ operator returns the wrong record (just shortsighted implementation by Microsoft)

 

[Jet Fusion]

Hi Peter,

Thank you for your reply... It does what I need and thanks for that and the tips above, noted. Is there a way to have the table increases the size of the table automatically and not having to manually pull to increase the size when new rows are added as per your attached?

So on your file, if you add rows below 9 on the Data sheet and when you go to Master sheet it only shows once you pull down on the left side to show more rows of the table?

 

[Peter Bartholomew]

I do not think it is possible to synchronise two tables without using VBA. The logic would be
(i) If a change is made to the data table count the rows in both master and data
(ii) If they are different resize the master table, otherwise exit.

The alternative more standard spreadsheet practice is to over-size the dependent table. Then formula changes and/or conditional formatting is needed to supress errors in the unused part of the master table.

 

[Jet Fusion]

Ah that makes sense to do it with VBA rather but this is good for what i need it fo.

Thank you so very much for your help. Much appreciated.