Change a cell manually, by code and by scrollbar

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!

Hi there,

What you want can almost certainly be done, but you have a much better chance of receiving meaningful assistance if you can let us see what your input worksheet looks like (a sample workbook NOT a screenshot!). It's much easier for us to "play" with something you've already created rather than to try to recreate it based on your description.

Regards,

Greg M

Originally Posted by Greg M

Hi there,

What you want can almost certainly be done, but you have a much better chance of receiving meaningful assistance if you can let us see what your input worksheet looks like (a sample workbook NOT a screenshot!). It's much easier for us to "play" with something you've already created rather than to try to recreate it based on your description.

Regards,

Greg M

Hi Greg M, Thanks for your reply! Indeed a good point. I made an example excel file, I hope this is sufficient.

Kind regards

Hi again,

Take a look at the attached version of your workbook and see if it gets you moving in the right direction.

The worksheet contains cells for your "predefined" values (i.e. from the dropdown list), cells for manual data entry if required, and a facility to adjust the actual percentages by using scrollbars. Non-zero values entered manually will take precedence over the "predefined" values.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

Wow this is awesome! Yes this is exactly what I was looking for thank you very much! I see the formula you used in the actual percentages. Brilliant solution, it looks so simple but I would have never thought of that. I will immediately throw this in my model thank you again for resolving this issue!!

Kind regards

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M