use a worksheet like a formula

[mikebo]

We all know how you can use a formula to calculate something and display the
result. Basic excel functionality. But can a worksheet be used like a
formula? What I want to do is the following:

I have a worksheet W1 that has a lengthy calculation, based on certain cells
used as an input. Output are 2 numbers in different cells. Just your
run-of-the-mill stuff.

Now, what I would like to do is to set up another worksheet W2, define a
range of input values, then tell Excel: Take those input values to my
calculation sheet in cells A1, A2, etc. (or wherever the input values need to
go), and then ad the results on the sheet W2 in the cells A4 and A5.

That way I could very easily vary the input variables and get a table of
results that I could then further analyze.

Perhaps it is easier if I describe it this way. I would like to have a table
that looks something like this:

Input 1 Input 2 Result 1 Result 2
10 200 24 .3
20 200 30 .1
30 200 48 .5
10 300 7 2
20 300 5 4
30 300 6 10

etc.

Where Results 1 and Resutls 2 come from calculations done on a worksheet,
not a formula.

Is that possible in Excel?

 

[Sean Timmons]

May need to know more about your W1.

I assume you mean cells C1 and D1, not A4 and A5... so do you have some sort
of list that you are matching up to the Input values in columns A and B?

I'm thinking maybe you'll be wanting to use VLOOKUP() so you can search for
your Input 1 value and return a value from another column on your W1
worksheet and the same for Input 2. But, not sure if that's what you have
going on with worksheet W1...

 

[mikebo]

Sorry. Don't mix the two descriptions

Yes, what I want to do is to set up a range of input values (such as listed
in colums 1 and 2 in my example), then calculate (using a worksheet) results
based on these input values, and finally write the results back into the
sheet containing the input values (in this case Columns 3 and 4).
I checked the VLOOKUP function, and as far as I can see it does not what I
want. It simply finds a number in a list and returns the matching number in
another list.
I did look up goal search and scenarios, but they won't do what I want either.