R
Roger on Excel
[Excel 2003]
I have a problem with data storage and it involves the use of pivot tables
and the changing order in which it arranges entries when updated
I use Excel for chemical process evaluation and I enter a process into the
sheets. The process steps are in column C and the Materials are put into
column D and the quantities in Column E.
Using a pivot table, one can sum up each chemical and its total amount used
by reading and consolidating down columns D and E. This works great.
Once the pivot table is populated I use vlookup (to a chemical database in
the spreadsheet) to populate a column next to the pivot table to enter the
density for each item in the table. When there is no vlookup item, I have to
enter the density manually adjacent to the pivot entry.
Again this is fine, however, here is my problem.
If I edit the chemical process and change chemicals in column D, when I
update the pivot table, sometimes the pivot table list order of chemicals
changes and so the hand entered densities no longer match up with the right
entry in the table.
Ideally, what I would like is that each time I update the pivot table,
elsewhere (in the same sheet (say 20 rows below the pivot table) the
chemicals and their density (either from vlookup or the hand entered value is
stored in the sheet)
I would need new or changed items to be added to the chemical/density list
each time the table is updated.
Perhaps once there is a chemical/density list in the sheet, the vllookup
will check items in that list (as well as the chemical database) for
densities for chemicals in the pivot table and once dnesity is added again
next to newly added items in the pivot table, those items and densities will
be added to the list below.
I know this sounds complicated, but this changing of order of items is a
fundamental problem as it also affects other items i want to add next to the
pivot table such as price and catalog# and melting point etc.
Perhaps another way is for Excel to add items in the pivot table to the
chemical database? But I am not sure how to automate this.
Can anyone help?
Many thanks,
Roger
I have a problem with data storage and it involves the use of pivot tables
and the changing order in which it arranges entries when updated
I use Excel for chemical process evaluation and I enter a process into the
sheets. The process steps are in column C and the Materials are put into
column D and the quantities in Column E.
Using a pivot table, one can sum up each chemical and its total amount used
by reading and consolidating down columns D and E. This works great.
Once the pivot table is populated I use vlookup (to a chemical database in
the spreadsheet) to populate a column next to the pivot table to enter the
density for each item in the table. When there is no vlookup item, I have to
enter the density manually adjacent to the pivot entry.
Again this is fine, however, here is my problem.
If I edit the chemical process and change chemicals in column D, when I
update the pivot table, sometimes the pivot table list order of chemicals
changes and so the hand entered densities no longer match up with the right
entry in the table.
Ideally, what I would like is that each time I update the pivot table,
elsewhere (in the same sheet (say 20 rows below the pivot table) the
chemicals and their density (either from vlookup or the hand entered value is
stored in the sheet)
I would need new or changed items to be added to the chemical/density list
each time the table is updated.
Perhaps once there is a chemical/density list in the sheet, the vllookup
will check items in that list (as well as the chemical database) for
densities for chemicals in the pivot table and once dnesity is added again
next to newly added items in the pivot table, those items and densities will
be added to the list below.
I know this sounds complicated, but this changing of order of items is a
fundamental problem as it also affects other items i want to add next to the
pivot table such as price and catalog# and melting point etc.
Perhaps another way is for Excel to add items in the pivot table to the
chemical database? But I am not sure how to automate this.
Can anyone help?
Many thanks,
Roger