Generate 1-to-1 outputs for a list of inputs into a excel formula

[Guest]

I have a detailed workbook in excel that is using a series of approximately
50 formulas and lookup tables to generate a result based on a few inputs that
are fed into the formulas. While I understand, and consider myself to be
proficient with macros, I was wondering if there is any way that I could
create a list of several hundred input parameters that would be fed into the
equations and have excel organize the answers that are generated.

 

[Guest]

The quick answer is yes. The longer part of the answer involves how you want
to provide the input parameters. I would assume that they need to be easily
altered so you can look at different results based on different inputs.

The easiest way is probably something like adding a sheet to the workbook
that you would use to enter the parameters and have your formulas pick up the
values from it.

Simple Example:
You have a formula somewhere: =(5 + 4)^2
On your new sheet you put 5 in A1, 4 in B2 (and the sheet's name is Sheet2)
You could revise your formula to: =(Sheet2!A1 + Sheet2!B2)^2

Other alternatives would be retrieving the data items from a text file. I
work with some people who have laboratory test results that the test
equipment creates .txt files containing the test results and we just read
those right into Excel spreadsheets for analysis. Similarly you could
retrieve the parameters from other sources such as a relational database.

In the cases where you are retrieving the parameters from external sources
(.txt files, databases, etc) you would then have the code place the parameter
items into specific locations in your workbook and attack it like the simple
example above.

Hope this assists you in your search for an answer. Just remember that
pretty much anything you can do from the keyboard, you can do within a macro
although you may not always be able to record a macro to do it all. The
macro recorder is very literal minded and often custom modification or just
from the ground up building of custom code is needed to provide a solution to
a complex activity.