Hello -
This may be beyond my current capabilities, but I have multiple 'calculators' set up on separate worksheets to perform a basic calculation done on a user entered value. Instead of having the user select each worksheet tab for each individual product, I would like for them to be able to select the product from a drop down, then enter a value and have the calculation done on that entry and return a result. I would like for this to be on just one worksheet or via a basic user interface.
Would this require VBA to accomplish? I have a basic to intermediate skill level within Excel.
Any thoughts on the simplest method to accomplish this would be very helpful.
Thank you,
Dave
Hi Dave
Without looking at your calculators it is a bit difficult to guess what you need. Depending on the complexity, you may not need any VBA (and most don't). You can create your drop-down via Data Validation and then link the selection to the calculators.
If you can share your calculators with us, it would be easier to assist.
Sunny
SunnyKow said
Hi DaveWithout looking at your calculators it is a bit difficult to guess what you need. Depending on the complexity, you may not need any VBA (and most don't). You can create your drop-down via Data Validation and then link the selection to the calculators.
If you can share your calculators with us, it would be easier to assist.
Sunny
Thanks for the input Sunny. I've attached an example of one of the calculators mentioned. I have 12 or 13 other ones and the list will expand. As I mentioned, each is on a different worksheet currently. The format of these calculators is not critical, I only need a field for a user entered value, and a field for the calculated result. In these examples, the user enters the value in the box, and the result displays in red.
Hi Dave
There can be many ways to create what you wanted. See my attachment for one example.
Hope this helps.
Sunny
Thanks again, I can use that. I will continue to work on getting all calculators onto one worksheet, rather than a separate sheet for each.
Hi Sunny - Can you tell me what the apostrophe inside the double quotations does in this example using INDIRECT? I didn't notice this at first.
INDIRECT("'"&B5&"'!A1")
Thanks again,
Dave
Hi Dave
The apostrophe is required in your case as the worksheet name referred to consist of more than one word.
The normal formula would look like this ='Calculator A'!A1
I need to join the apostrophes to the sheet's name so I need to put them in double quotes before joining.
If your sheet's name consist of only one word, then the apostrophe is not required e.g. =CalculatorA!A1
Hope this is clear to you.
Sunny
OK, I see. I'm not familiar with INDIRECT, so that helps. After using this at work, I do want to get away from having a separate sheet for each calculator, any suggestions on a straightforward approach to that?
Hi Dave
It is difficult to give an answer without looking at some sample calculations.
Maybe you can try something like the attached.
Sunny
