Not sure how to ask or formulate this Question

[artcank]

So I'm not really sure what the right question is but let me try to explain.

I'm using a spreadsheet that the user can input(from a dropdown) a material(B18) and an amount of that material(C18).

This then calculates (based on all the materials selected and their amounts) the total molecular weight of each element present in the complete recipe(formula). This is based on a lookup table that has the molecular weight of elements in each material.

the sheet displays the amount of each element including the ratios of silica and alumina

I want to be able to lock certain values in the displayed chart of elements (like 2% SiO2 and .3 Alkali Metals) then be able to adjust some of the amounts of the materials in the fields below.

Changing these amounts would also change the amounts of SiO2 and the Alkali Metals unless you increase or decrease the amounts of the materials that supplies those elements.

The values that were locked above would automatically adjust the amounts based on the materials that the user did adjust.


Does this make any sense?

 

[severynm]

Welcome!

I want to be able to lock certain values in the displayed chart of elements (like 2% SiO2 and .3 Alkali Metals) then be able to adjust some of the amounts of the materials in the fields below.

Changing these amounts would also change the amounts of SiO2 and the Alkali Metals unless you increase or decrease the amounts of the materials that supplies those elements.

So for these cells in your screenshot (B7, D7, E7, for example) you want them to sometimes be formulaic and sometimes be a "hardcoded" user input? Based on what you described, does this explain the process you are looking for? Cells B7, D7, E7, etc start start out formulaic and would calculate based on what the user inputs in B18:Cxx. You then want to preserve (some? or all?) the calculated values in B7, D7, E7, etc. The user would then go back to B18:Cxx to make adjustments, which mean B7, D7, E7 etc would need recalculating.

I'm not sure that makes sense to me - can you be a little more specific about your desired process. In your example, can you be specific about which cells are being changed and which should stay constant? Please use cell ranges as I have little chemistry familiarity so it won't mean much to me otherwise.

 

[artcank]

I'll try.
Notice in these jpegs that D5 and F5 stay the same 0.3 and 0.7. These are the sums of the the values in their respective colors.
What is changing is B7 (the amount of SiO2) or D7 (the amount of Al2O3). (These can change also but not in this example.)
They are populated from a chart based on the Material(B18-B22) and amount (C18-C22).

Making a single change here (C18-C22) can cause any or all of the values in the above fields to change.
So let's say I want to make sure D5 and F5 stay the same 0.3 and 0.7 as well as B7 but I want to increase my collective Al2O3 to 13
I have to go down below and change the amount of EPK (C21) to 12.
However, this also changes the amount if SiO2 in B7.
This causes me to "play" with the other amounts in C18-C22 to lower the SiO2 level back to 2.0.

Is there any way to "lock" a value in that I want to keep(B7) and have excel adjust the amounts(C18-C22) to "fit"?

Does this explain it any better?

Thanks