Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
user defined functions
Hello:
I have a nested function that is uncomfortably long for typing: =SUMPRODUCT(SUMIF(INDIRECT("'" & wslist & "'!A1"),"<0",INDIRECT("'"& wslist & "'!A1"))) As is, the function applies to the A1 cell. If I wish to use it with another cell, say C3, (but the same wslist) I would type =SUMPRODUCT(SUMIF(INDIRECT("'" & wslist & "'!C3"),"<0",INDIRECT("'"& wslist & "'!C3"))) Is it possible to define a function, e.g. MySum(cell) such that the above examples would reduce to MySum(A1) and MySum(C3), respectively? I was advised on another group of a great replacement =SUMPRODUCT(SUMIF(INDIRECT("'" & wslist & "'!" & ADDRESS(ROW(A1),COLUMN(A1),4)),"<0",INDIRECT("'"& wslist & "'!" & ADDRESS(ROW(A1),COLUMN(A1),4)))) which proves that being longer is not necessarily being less amenable to typing, for it now can be copied and pasted. Still, if it were possible to use a simple name like MySum for the whole expression it would be less intimidating. Thank you, Alex |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
user defined functions
Could you say what it is you are trying to achieve? What values are
involved and in what cells? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
user defined functions
Tushar Mehta wrote:
See the response to your post in .newusers from yesterday. Saw it. It does not work. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
user defined functions
Well,
imagine a stack of spreadsheets with names product1, product2, etc. On top of those is a spreadsheet with name, say, "production unit." Some cells in the latter show sums of the underlying cells from "product" sheets, not necessarily all of them, but according to what is included in wslist. This is what the formula I mentioned previously does for the cell A1. Since it is somewhat long and in fact contains only one parameter, viz., the cell name, I sought perhaps there is a way to specify a function with a single argument that takes a cell address and returns the result computed by the formula. In regular programming language: C, C++, Java, Fortran, Pascal, perl, you name it ... this is a trivial matter. I found no indication in the Excel's help it allows to do such a simple thing. Since I am doing that for a client who might be prone to mistype the formula, an abbreviation would be appreciated. Having to copy and paste is the next best thing. So I very much appreciate the reply I received on another group. Still, is it possible to define such a function in Excel? Thank you for your interest. Alexander Bogomolny Bigwheel wrote: Could you say what it is you are trying to achieve? What values are involved and in what cells? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Functions | Excel Worksheet Functions | |||
User Defined Functions | Excel Worksheet Functions | |||
user defined functions | Excel Programming | |||
excel functions and User defined functions | Excel Programming | |||
User Defined Functions | Excel Programming |