Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
Its a massive financial calculation, up to 30 worksheets and uses a lot of
functions and operators and conditionals. It takes 2 parameters as input and produces one result output. I want to call this massive function over and over again from within the same workbook, but with different input parameters (and lets say chart the output or create a table of the output). Thanks "Jacob Skaria" wrote: 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
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. |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to call a worksheet (as a function)
Thanks DHStein, I believe what you say is my solution. It will take a few
days for me to try out both methods and will be back. Also thanks to Jacob and Dave. There should be a method in Excel to Define a NAME for a piece of WS calculation (in the same way that names can be defined for cells), and then call that function name "=UDF(....)" and treat it as a simple function. All the Define has to do is to associate the function parameters to cells in the worksheet. Of course the question arises, if you switch to the worksheet, what values will you see there? The last calculation? "dhstein" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a do not call function? | Excel Discussion (Misc queries) | |||
how to call the event of other worksheet | Excel Worksheet Functions | |||
to call procedure in a worksheet in a module | Excel Discussion (Misc queries) | |||
how i can call the function | Excel Worksheet Functions | |||
How to call worksheet functions from Access ? | Excel Worksheet Functions |