Copying which cell is called with sheet names in drop-down menus

[Exothermaniac]

Hello,

I'm putting together a workbook for quick unit conversion of elemental analysis values. Adding a sheet dedicated to calculating composition of blends of these materials and additives has got me stumped. Currently I have a large selection of elements in the periodic table in separate sheets containing analysis values. The sheets are called Material_1, Material_2 and Additive_1, Additive_2 and so on. These names have been placed in a data validation list so I can get a drop-down menu choosing which materials to blend. Now I want to call two (at least) cells containing sheet names from the data validation lists with the indirect command but it's a pain - the first cell references in the data alright but I can't copy the formula so it updates the actual cell to be read in the data sheets that are called. I assume this is due to the fact that I'm using the Indirect command where I'm referencing the actual cell to be read as text (using quotation marks).

With about 70 elements in the list and four different units to convert it into (mol/kg, mg/kg, etc...), that will be a whole lot of manual updates to do. Am I working the Indirect command wrong or should I just think smarter?

Currently I'm using this type of line:

=$C$4/100*INDIRECT($B$4&"!I14")+$C$5/100*INDIRECT($B$5&"!I14")

C4 and C5 contains the relative ratio in the blend provided as percents provided by the user. B4 and B5 contains the sheet names from the data validation drop list and I14 is the cell I'm calling, obviously. I would like the number to update automatically so I don't have to go through 280 updates that I might need to do again if there's a change in the sheet by someone else.

[Jacc]

I'm not sure I understand but here is my advice.
Put the indirect formulas in a named range and use the named range as a list in the datavalidation.