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.

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.

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

"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.

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

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


"Bernie" wrote:

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

"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.

Hi - it will not be in another workbook. It can even be on the same worksheet.

UDF ... OK, its the first time I hear this. Pls. let me check it out.
Thanks.

Bernie



"Jacob Skaria" wrote:

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

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


"Bernie" wrote:

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

"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.

Does UDF require VBA programming? The calculation is about 30 worksheets
long, and about 50 lines on each worksheet, and 200 cells on each line. Are
you saying I have to rewrite 300,000 cells or thousands of arrays in VB?

Now I am really confused. If I already have a working set of worksheets,
why do I have to rewrite the whole thing into VB to make a UDF out of that?

If I just have to have a 1 liner VBA, could you then pls. write it for me,
and tell me how to enter that into Excel? Thanks a lot.

thanks

"Jacob Skaria" wrote:

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

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


"Bernie" wrote:

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

"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.

Sorry, could you please let us know what the calculation is about..and what
formula is used..
--
If this post helps click Yes
---------------
Jacob Skaria


"Bernie" wrote:

Does UDF require VBA programming? The calculation is about 30 worksheets
long, and about 50 lines on each worksheet, and 200 cells on each line. Are
you saying I have to rewrite 300,000 cells or thousands of arrays in VB?

Now I am really confused. If I already have a working set of worksheets,
why do I have to rewrite the whole thing into VB to make a UDF out of that?

If I just have to have a 1 liner VBA, could you then pls. write it for me,
and tell me how to enter that into Excel? Thanks a lot.

thanks

"Jacob Skaria" wrote:

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

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


"Bernie" wrote:

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

"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.

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.

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.

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.

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.

Hi Bernie,
When you say:

"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'm not sure what you mean. Could you give an example of the formulas you
are currently using in Column C?

Regards - Dave.

Hi Dave - let me start over - I have a 30 page worksheet that takes 2 inputs
and creates one result.

On another page of same workbook, I want to "call" this worksheet 100 times
with different input parameters and get the 100 results. This could be a
table for example. How do I call a worksheet or calculation (very
complicated calculation that cannot be put in VB as its like 2000 lines of
code)?

The calculation done to the 2 input parameters 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 100 times with 100 different input parameters.
Obviously I cant write a 300,000 cell, 2000 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again.

So imagine a table 100 rows and 3 columns. The first two parameters are in
the first 2 columns and the result goes into the 3rd column on the same row.
How do I fill this table (dont want to use macros or VBA)?

"Dave" wrote:

Hi Bernie,
When you say:

"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'm not sure what you mean. Could you give an example of the formulas you
are currently using in Column C?

Regards - Dave.