spreadsheet as function

dbertenshaw · Apr 15th, 2002, 08:11 AM

I have developed and spreadsheet of quite complex calculation using numerical approximation, and would like to use it as a function. I want to be able to call its calculation by passing a value or values to certain cells, then get back the computed values. I would then use this in another worksheet in multiple cells to perform analysis of other data already in that sheet.

In an ideal world, from sheet2 I would be be able to
CALL(sheet1!A1B2=C2B2, S4E4=sheet1!D3F4) where sheet1 contained the function calculation.

Since I want to embed this call in many multiple cells in sheet2, then a macro doesnt seem to be the right thing.

Any ideas??

[ This Message was edited by: dbertenshaw on 2002-04-15 07:13 ]

WillR · Apr 15th, 2002, 08:15 AM

You may want to try writing a UDF (User defined function) in VB to do this calc instead of referring to a set of calcs in another worksheet... not sure what to suggest without further info, but you can get some examples of UDFs from Aaron Blood's excel site

http://geocities.com/aaronblood/

might be able to help myself if I knew more about your complex calc...

Will

Chris Davison · Apr 15th, 2002, 01:35 PM

Yeppers, and also create a named formula, works from sheet to sheet, or an actual add-in, works from book to book

Apr 15th, 2002, 01:35 PM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Yeppers, and also create a named formula, works from sheet to sheet, or an actual add-in, works from book to book __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 08:11 AM	#1
dbertenshaw New Member Join Date: Apr 2002 Location: David Bertenshaw Posts: 1	I have developed and spreadsheet of quite complex calculation using numerical approximation, and would like to use it as a function. I want to be able to call its calculation by passing a value or values to certain cells, then get back the computed values. I would then use this in another worksheet in multiple cells to perform analysis of other data already in that sheet. In an ideal world, from sheet2 I would be be able to CALL(sheet1!A1B2=C2B2, S4E4=sheet1!D3F4) where sheet1 contained the function calculation. Since I want to embed this call in many multiple cells in sheet2, then a macro doesnt seem to be the right thing. Any ideas?? [ This Message was edited by: dbertenshaw on 2002-04-15 07:13 ]

Apr 15th, 2002, 08:15 AM	#2
WillR Board Regular Join Date: Feb 2002 Location: Sutton Coldfield Posts: 1,143	You may want to try writing a UDF (User defined function) in VB to do this calc instead of referring to a set of calcs in another worksheet... not sure what to suggest without further info, but you can get some examples of UDFs from Aaron Blood's excel site http://geocities.com/aaronblood/ might be able to help myself if I knew more about your complex calc... Will