Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a sheet that takes 10 input parameters and a whole bunch of caluculations.

Now I would like Excel to do the calculations on another array of input parameters, without having to copy the sheet.

Actually, I would like to be able to easily switch between different arrays of input parameters. Ideally, I can store these input parameters for all scenarios on a different sheet, and then select the desired on in the main sheet using a drop box.

Can somebody point me into the right direction? Does anybody know about an example for this?

Thank you! J.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

Your idea is fine for me. You can set up a table in an empty sheet where first column lists parameter meaningful names, and each one of the successive columns contains a different parameter value set. Then, on the "bunch calculation" sheet you set up a unique cell to choose a column of the table in the other sheet, and in the cells containing parameters you have to define a formula that using INDEX function retrieves the right value from the parameters table. So, changing the value of only one cell you realize istantly a whole different calculation without having to select a different sheet.

share|improve this answer

You can use the INDIRECT() function.

Sheet1

Input1  111
Input2  222

Sheet2

Input1  42
Input2  43

Sheet3

Sheet1
=INDIRECT(A1&"!"&"A1")+INDIRECT(A1&"!"&"A2")

Now Sheet3!A2 displays "333". If you change Sheet3!A1 to "Sheet2", then Sheet3!A2 will display "85". You can simplify your calculation formulas by not mixing in a whole bunch of INDIRECT() functions if you isolate them to an area on Sheet3 (or even its own sheet) and have your formulas only do their normal thing but referencing this intermediate area.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.