How to turn a spreadsheet into a function?

[h7qvnk7q001]

Suppose I have a spreadsheet that performs a complicated calculation,
involving LOOKUP functions, and other structures that are not easily
expressed in a single-cell formula or macro.

I would like to treat the whole sheet as a formula, with one cell as
the input, and another as the output. Then on another sheet, I would
like to use that formula, possibly multiple times with different input
values each time (to generate charts or graphs with the variable input
values for example).

Is there any way to define a spreadsheet as a function in this manner?
If not, is there another way to approach this problem?

Thanks,

Tim.

 

[Bernie Deitrick]

Tim,

You would need to use a macro to step through your input values, force a calculation, and store the
output.

For example, let's say that your input cell is Sheet1 A1, and your output cell is Sheet1 B10. On
Sheet2, you have a list of input values, starting in cell A2 and going down the column, and you want
the outputs in Sheet2 column B, on the same row - the macro below shows how to do it.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range
Dim myRange As Range

With Worksheets("Sheet2")
Set myRange = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
End With

For Each myCell In myRange
Worksheets("Sheet1").Range("A1").Value = myCell.Value
Application.CalculateFull
myCell(1, 2).Value = Worksheets("Sheet1").Range("B10").Value
Next myCell

End Sub