user defined functions

[Alexander Bogomolny]

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

 

[Tushar Mehta]

See the response to your post in .newusers from yesterday.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bigwheel]

Could you say what it is you are trying to achieve? What values are
involved and in what cells?

 

[Alexander Bogomolny]

See the response to your post in .newusers from yesterday.

Saw it. It does not work.

 

[Alexander Bogomolny]

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