Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default How to turn a spreadsheet into a function?

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default How to turn a spreadsheet into a function?

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


wrote in message
oups.com...
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I turn the #N/A result from a lookup function to a zero? Chase Excel Worksheet Functions 4 May 16th 08 09:10 PM
If, Then Function Dee Excel Worksheet Functions 5 July 19th 05 11:48 PM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
how to move user defined function Grant Excel Worksheet Functions 1 November 17th 04 07:38 PM


All times are GMT +1. The time now is 07:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"