User defined functions

  • Thread starter Thread starter Alexander Bogomolny
  • Start date Start date
A

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?

Thank you,
Alex
 
Instead of typing the whole formula all over again,
you could try copying the original, i.e.
=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist & "'!A1"),"<>0",
INDIRECT("'"& wslist & "'!A1")))
Click to expand...

and paste into the cell where you want to put the formula

Then just click Edit > Replace to change the ref "A1" to "C3", viz:

In "Find what:" box: A1
In "Replace with:" box: C3

Click "Replace" button

--
Or perhaps try replacing the static "'!A1" parts
in the original formula with:
ADDRESS(ROW(A1),COLUMN(A1),4)

i.e. try instead (untested):

=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist &
"'!"&ADDRESS(ROW(A1),COLUMN(A1),4)),"<>0",INDIRECT("'"& wslist &
"'!"&ADDRESS(ROW(A1),COLUMN(A1),4))))

The above would now copy relatively across and down, i.e.:
as you copy across, cell ref will change from A1 to B1 to C1, etc
and as you copy down cell ref will change from A1 to A2 to A3, etc
 
Thank you. This is indeed much easier.

Alex
Instead of typing the whole formula all over again,
you could try copying the original, i.e.


and paste into the cell where you want to put the formula

Then just click Edit > Replace to change the ref "A1" to "C3", viz:

In "Find what:" box: A1
In "Replace with:" box: C3

Click "Replace" button

--
Or perhaps try replacing the static "'!A1" parts
in the original formula with:
ADDRESS(ROW(A1),COLUMN(A1),4)

i.e. try instead (untested):

=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist &
"'!"&ADDRESS(ROW(A1),COLUMN(A1),4)),"<>0",INDIRECT("'"& wslist &
"'!"&ADDRESS(ROW(A1),COLUMN(A1),4))))

The above would now copy relatively across and down, i.e.:
as you copy across, cell ref will change from A1 to B1 to C1, etc
and as you copy down cell ref will change from A1 to A2 to A3, etc
Click to expand...
 
Ummm...you are checking a single cell. The SUMIF and SUMPRODUCT
functions serve no useful purpose. From what I can tell you will get
the same result if you use the formula =INDIRECT("'" & wslist & "'!A1")

--
Regards,

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