I have a worksheet with an input at cell J2, many calculations, and a series of outputs at cells K4167:Z4167.




I want to run the worksheet several hundred times for different values of the input and produce a table showing the outputs for each input.


How does one do this in Excel?


Thank you.

You'll want to COPY VALUES ONLY from cells K4167:Z4167 each time it changes.

Then PASTE SPECIAL VALUES to the first empty in a blank worksheet. Paste to A1 to begin with ... the code will do the rest.

When ready, you'll have a table of data to view.

Google for examples for the terms in bold. Tons of examples to review.

Thanks, Logit. If I understand correctly, you are suggesting manually changing the independent variable and then manually copying and pasting the output to save it? If so, that is practical for a small number of cases. But I have many hundreds of cases to test each, on a number of different variants of the basic worksheet. So I am looking for an automated solution.

No ... nothing manual.

The bolded, key terms I listed are used in VBA Coding. Creating macros that utilize these terms will automate the functions you are seeking.

And ... there are plenty of complete example code you can simply copy and paste into your project.

Go on, I dare you; attach a sample workbook outlining the range of independent variables, and some samples of the 'different variants of the basic worksheet'.


You may find Switch between various sets of values by using scenarios useful where:
"A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results. If several people have specific information that you want to use in scenarios, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one.
After you have all the scenarios you need, you can create a scenario summary report that incorporates information from all the scenarios."