Table syncing

[madeleine101]

I am currently writing an excel workbook to analyse material contents of various yachts for Life-Cycle analysis and was wondering if you may be able to help solve an issue I have?

Attached is an example file which I have made with ‘Vessel 1’ sheet showing the referencing style, and ‘Vessel 2’ with no referencing. The codes are intentionally duplicated in some places so they cannot be used as ID.

What I am aiming for is to have the “Primary_Data” table to be where rows are added and amended, and then for these amendments to automatically sync with the vessel specific tables. At the moment, if I were to link the first three columns (Code, material, and Dis. Type) using ‘=’ referencing, they would sync but the following columns in the vessel specific tables (Mass and %) stay as they were. So for example, if I were to add a row between fuel and teak, the vessel tables update those columns, however the mass and % values do not move and would incorrectly display the ‘test’ as having a mass, this test mass and % is actually the teaks...

If there a way to fix this with VBA coding or queries?

[#2]

@madeleine101 , could you use as ID Code&Material&Dis.Type or so?

[#3]

HI@Sergei Baklan 

I tried using an ID code for the whole table, also tried splitting the table into two and using ID codes for both but neither seem to work as when a row is added, the cells still do not sync or jump correctly...

[#4]

@madeleine101 

Did you try VLOOKUP or INDEX/MATCH on this combined ID? Perhaps you may attach sample file removing all sensitive information to play with it.

[#5]

Hi @Sergei Baklan 

I did try both.. 

[#6]

@madeleine101 

In theory it shall work. Could you share formulas you used, or better the sample?

[#7]

Hi @Sergei Baklan 

Are you able to see this attachment?

Vessel 1 only is using direct ref, vessel two has the ID and Index(Match.. neither are working as can be seen with the 'test' which should not have a mass in the cell...

[#8]

@madeleine101 

Yes thank you. If we consider PrimaryTable and Vessel2:

Let add one more row into first one with ID=9

After that add empty row into Vessel2 and enter 9 into ID

It works, Mass is zero since we didn't add it so far.

[#9]

@Sergei Baklan 

So have you kept the same equation and then manually typed in the ID of the new row into the vessel2 table?

[#10]

@madeleine101 

Yes, I didn't touch your formulas, only added ID

[#11]

@Sergei Baklan 

Great thank you!

It will work for now however I am really aiming for an automated system as it will be used by multiple people and I can see there will be some mistakes along the way as the real document has hundreds of lines and entries....

[#12]

@madeleine101 

Perhaps you may use more meaningful IDs rather than sequential numbers, more understandable by end users. Afraid without any IDs it'll be much more mistakes.