cannot get concatenated formula to evaluate...
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 |
cannot get concatenated formula to evaluate...
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 |
cannot get concatenated formula to evaluate...
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 |
All times are GMT +1. The time now is 11:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com