Hi everyone,

I am creating an excel model to calculate CO2 emissions from certain production processes. I want to make it possible for users of the model to alter ingoing materials into the system (by changing their percentages). I want different options of changing the cell values. Example:

material A: 20%
material B: 40%
material C: 40%

Option 1: select a 'product' (which means a prefabricated division of percentages) e.g. product 1 has A=20%, B=40%, C=40% and product 2 has A=30%, B=50%, C=20% and so on...
I achieved this by making a table with all products (1,2 3 ..) with their according material percentages. I used the SUMIF function to refer to the table and then I linked this to a dropdown menu. So in the dropdown menu you can select product (1,2 or 3 etc.) and then the corresponding percentages will appear.

Option 2: Manually enter a value. This is where the first problem arises.. Because after you manually changed the cell once, the formula in the cell disappears, which means that after that you cannot use Option 1 anymore (the dropdown menu).


Option 3: I also want to add a scroller. So that instead of manually filling in the cell values, the user can also play around with the percentages by using scrollers. Is it possible to make 3 scrollers that are linked to 3 cells and that they together cannot exceed 100% and will change accordingly?



So in summary: I want cells that can change by different input options, WITHOUT comprimisng the other option of input via:
1. dropdownmenu + SUMIF function
2. manually
3. using a several scrollbars all linked to 1 cell (in a way that the combination of percentages do not exceed 100%)


I hope I was able clearly address what I am trying to achieve.
I find myself searching the internet but I haven't found a solution. Probably I am just using the wrong search terms or something..
I would really appreaciate it if someone can lead me in the right direction!