You have 2 choices here.

Option 1)
Move all formulas around so they are all on the same row. So the first
parameter is in cell A2, the second parameter is in cell B2 the result is in
cell C2. your first calculation is in cell D2, second calc in cell E2 . If
you need other worksheets, all of those calculations are in Row 2 using
whatever columns you need. Then copy all formulas down and you can build
your table that way. In that case the first result is in row 2, the second
result is in row 3, etc. You have a lot of worksheets so this may not be a
good option.

Option 2)
Write a simple VBA loop that takes values from a table somewhere - say
WSTABLE!A1 and WSTABLE!B1, puts them into your cells WS1!A1, and WS1!A2 -
then takes the value in WS1!B1 and copies it into WSTABLE!C1 -

Something like this (Not debugged - may have errors - just as an example):

Sub MakeTable()

Dim J As Integer

Dim Range1 As String
Dim Range2 As String
Dim Range3 As String


For J = 1 To 100

Range1 = "WSTABLE!A" & J
Range2 = "WSTABLE!B" & J
Range3 = "WSTABLE!C" & J

Range("WS1!A1").Value = Range(Range1).Value
Range("WS1!A2").Value = Range(Range2).Value

' Might need a calc statement here - not sure about that

Range(Range3).Value = Range("WS1!B1").Value

Next J


End Sub

"Bernie" wrote:

OK I understand - but are you saying the UDF I have to write will be as large
as the 30 page worksheets (thousands of lines)? Or are you saying there is
an easy way to write a UDF (that uses the worksheet). Please be clear. I
really dont want to write a huge UDF when I already have a debugged huge
worksheet.

"Jacob Skaria" wrote:

If it is a table you can lookup and get the values. You cannot pass values
and return the calculations. You need to write a UDF once which does the
calc's and then use the UDF from cells as shown below..

which picks values from A1 and B1,. Does the calculation and returns the
value..

=MyUDF(A1,B1)

If this post helps click Yes
---------------
Jacob Skaria


"Bernie" wrote:

The calculation done to the 2 input parameters (1 and 11) is huge. Thousands
of NPV() and PPMT() and functions and conditional statements - you name it,
including trigonometrics etc. are used in these 30 pages of financial
calculations.

So I need to "call" this 500 times with 500 different input parameters.
Obviously I cant write a 300,000 cell, 1500 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again. I am not sure if I am answering your questions properly. But
what I need is called a function - i.e. making the worksheet set to act like
a function, that can be called with different input parameters. Its a very
simple thing really.

thanks

Bernie Glass

"Jacob Skaria" wrote:

Understood. Still you have not mentioned what is the calculation done to get
the result by passing 1 and 11.

Based on the formula you use to get the result; we should be able to make a
UDF

If this post helps click Yes
---------------
Jacob Skaria


"Bernie" wrote:

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

"Jacob Skaria" wrote:

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria


"Bernie" wrote:

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...


"Jacob Skaria" wrote:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


"Bernie" wrote:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.