Aug 18 2019 11:11 PM
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?
Aug 19 2019 03:41 AM
@madeleine101 , could you use as ID Code&Material&Dis.Type or so?
Aug 19 2019 04:13 AM
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...
Aug 19 2019 04:22 AM
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.
Aug 19 2019 04:36 AM
In theory it shall work. Could you share formulas you used, or better the sample?
Aug 19 2019 04:43 AM
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...
Aug 19 2019 05:59 AM
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.
Aug 19 2019 06:06 AM
So have you kept the same equation and then manually typed in the ID of the new row into the vessel2 table?
Aug 19 2019 06:20 AM
Yes, I didn't touch your formulas, only added ID
Aug 19 2019 06:41 AM
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....
Aug 19 2019 07:45 AM
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.