Custom Function to Retrieve Spread Sheet Data

I am pretty sure that I have done the following successfully many years
ago using my Excel97, but I can’t recall how.

My workbook has two spreadsheets. The first [TaxOnSell] computes CASHOUT
from PRICE and DOWNPAYMENT.

In the second, I want to build a table showing the amount of CASHOUT as
a function of various values for PRICE and DOWNPAYMENT.

My though was to insert a custom function in the various cells of the
second spreadsheet. The custom function would be of the form:

Function CASHOUT(DOWN, PRICE)
Worksheets("TaxOnSell").Range("E6").Value = PRICE
Worksheets("TaxOnSell").Range("B43").Value = DOWN
CASHOUT = Worksheets("TaxOnSell").Range("E58").Value
End Function

The function returns “#VALUE” , an indication I’m doing something wrong.

Can I use a custom function in the above described manner, and if so,
how do I get it to enter and retrieve the data?

You cannot change anything in a worksheet from within a function.
You should include all input to the function (like E58) in the argument list and not address cells directly.

What were the values of the arguments and the cells involved?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"windsurferLA" wrote in message . ..
| Custom Function to Retrieve Spread Sheet Data
|
| I am pretty sure that I have done the following successfully many years
| ago using my Excel97, but I can’t recall how.
|
| My workbook has two spreadsheets. The first [TaxOnSell] computes CASHOUT
| from PRICE and DOWNPAYMENT.
|
| In the second, I want to build a table showing the amount of CASHOUT as
| a function of various values for PRICE and DOWNPAYMENT.
|
| My though was to insert a custom function in the various cells of the
| second spreadsheet. The custom function would be of the form:
|
| Function CASHOUT(DOWN, PRICE)
| Worksheets("TaxOnSell").Range("E6").Value = PRICE
| Worksheets("TaxOnSell").Range("B43").Value = DOWN
| CASHOUT = Worksheets("TaxOnSell").Range("E58").Value
| End Function
|
| The function returns “#VALUE” , an indication I’m doing something wrong.
|
| Can I use a custom function in the above described manner, and if so,
| how do I get it to enter and retrieve the data?

The values of the arguments were non-integer numbers

If one can’t change anything in the first spreadsheet from with a
function in the second spread sheet, then my approach will not work.
I’m not sure how I did it in the past; perhaps I had written a macro
that cycled through all the cells in the second spread sheet, plugging
in and retrieving data from the first spreadsheet.

An approach to my problem may be to use Excel’s Scenario manager to
summarize the results from various combinations of inputs.


Niek Otten wrote:
You cannot change anything in a worksheet from within a function.
You should include all input to the function (like E58) in the argument list and not address cells directly.

What were the values of the arguments and the cells involved?