I've spent a lot of time today trying to get this to work, including
searching and reading posts and checking some referenced knowledge
base articles. Still no luck. Here's my problem:

=SUBSTITUTE(CONCATENATE("=sum(D4:D",endcell,")"),C HAR(34),"")


endcell is a named range - the last cell in column G that is greater
than zero returns a row number.

This evaluates nicely to:

=sum(D4:D238)

Trouble is I can't get it to work as a formula.

I checked all the referenced cells to make sure they were not
formatted as text and used F2 everytime I changed the format. Still
nothing.

Am I barking up the wrong tree or just missing something obvious? I
would be grateful for any guidance.

Andrew J. Fox

P.S. I wasn't sure whether to post this in the excel.misc or the plain
excel group. I chose this one because it seemed to have more traffic -
hope I was right.

ajf

There is nothing built in that will make that work, you can download
Morefunc
and in particular the EVAL function

http://xcell05.free.fr/english/




If you only want to sum D4:endcell where endcell holds a cell reference like
for instance D14 you can use

=SUM(INDIRECT("D4:"&endcell))


--
Regards,

Peo Sjoblom


"green fox" wrote in message
oups.com...
I've spent a lot of time today trying to get this to work, including
searching and reading posts and checking some referenced knowledge
base articles. Still no luck. Here's my problem:

=SUBSTITUTE(CONCATENATE("=sum(D4:D",endcell,")"),C HAR(34),"")


endcell is a named range - the last cell in column G that is greater
than zero returns a row number.

This evaluates nicely to:

=sum(D4:D238)

Trouble is I can't get it to work as a formula.

I checked all the referenced cells to make sure they were not
formatted as text and used F2 everytime I changed the format. Still
nothing.

Am I barking up the wrong tree or just missing something obvious? I
would be grateful for any guidance.

Andrew J. Fox

P.S. I wasn't sure whether to post this in the excel.misc or the plain
excel group. I chose this one because it seemed to have more traffic -
hope I was right.

ajf

How about:

=sum(d4:indirect("D" & endcell))
or
=sum(indirect("d4:d" & endcell))


green fox wrote:

I've spent a lot of time today trying to get this to work, including
searching and reading posts and checking some referenced knowledge
base articles. Still no luck. Here's my problem:

=SUBSTITUTE(CONCATENATE("=sum(D4:D",endcell,")"),C HAR(34),"")

endcell is a named range - the last cell in column G that is greater
than zero returns a row number.

This evaluates nicely to:

=sum(D4:D238)

Trouble is I can't get it to work as a formula.

I checked all the referenced cells to make sure they were not
formatted as text and used F2 everytime I changed the format. Still
nothing.

Am I barking up the wrong tree or just missing something obvious? I
would be grateful for any guidance.

Andrew J. Fox

P.S. I wasn't sure whether to post this in the excel.misc or the plain
excel group. I chose this one because it seemed to have more traffic -
hope I was right.

ajf

--

Dave Peterson