CONCATENATE to make a formula
 

Hi All,

I have a list of excel files what I produce a summary from the
calculations if the same for each cell that I want to enter only the
file name changes, I have writted the below function:-


=CONCATENATE("=SUM('[",'File list'!A1,E2,"]Monthly Charts'!$S$4:$W$5)/
5")

Which gets me the text that I want

=SUM('[Accounts Comms Sheet.xls]Monthly Charts'!$S$4:$W$5)/5

but is displays it as a text entry instead of been used as a function,
how can I get it to do the sum?

Cheers
Mark

CONCATENATE to make a formula
 

You need either to use XL4 macros which can crash Excel when copied in some
versions or VBA. There is not anything built in that will take a text string
that looks like a formula and then evaluate it.

Here's a link to a great add-in called Morefunc by Laurent Longre that has a
function called EVAL that will do this


http://xcell05.free.fr/



--
Regards,

Peo Sjoblom






"Sh0t2bts" wrote in message
oups.com...
Hi All,

I have a list of excel files what I produce a summary from the
calculations if the same for each cell that I want to enter only the
file name changes, I have writted the below function:-


=CONCATENATE("=SUM('[",'File list'!A1,E2,"]Monthly Charts'!$S$4:$W$5)/
5")

Which gets me the text that I want

=SUM('[Accounts Comms Sheet.xls]Monthly Charts'!$S$4:$W$5)/5

but is displays it as a text entry instead of been used as a function,
how can I get it to do the sum?

Cheers
Mark

CONCATENATE to make a formula
 

Thanks you for the fast reply :o)

CONCATENATE to make a formula
 

CONCATENATE is indeed a text function. You need the INDIRECT function.
You'll find details of it in Excel help.
--
David Biddulph

"Sh0t2bts" wrote in message
oups.com...
Hi All,

I have a list of excel files what I produce a summary from the
calculations if the same for each cell that I want to enter only the
file name changes, I have writted the below function:-

=CONCATENATE("=SUM('[",'File list'!A1,E2,"]Monthly Charts'!$S$4:$W$5)/
5")

Which gets me the text that I want

=SUM('[Accounts Comms Sheet.xls]Monthly Charts'!$S$4:$W$5)/5

but is displays it as a text entry instead of been used as a function,
how can I get it to do the sum?

Cheers
Mark

CONCATENATE to make a formula
 

CONCATENATE always generates a string; never a formula.
I cannot work out what you need.
What is the purpose of 'File list'!A1,E2?
It would seem you need to use the INDIRECT function not CONCAT
Please spell out what task you need to perform without reference to CONCAT
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Sh0t2bts" wrote in message
oups.com...
Hi All,

I have a list of excel files what I produce a summary from the
calculations if the same for each cell that I want to enter only the
file name changes, I have writted the below function:-


=CONCATENATE("=SUM('[",'File list'!A1,E2,"]Monthly Charts'!$S$4:$W$5)/
5")

Which gets me the text that I want

=SUM('[Accounts Comms Sheet.xls]Monthly Charts'!$S$4:$W$5)/5

but is displays it as a text entry instead of been used as a function,
how can I get it to do the sum?

Cheers
Mark

CONCATENATE to make a formula
 

This would be my guess using INDIRECT.

=SUM(INDIRECT("'[" & 'File list'!A1 & E2 & "]Monthly Charts'!$S$4:$W$5"))

HTH,
Paul

--

"Sh0t2bts" wrote in message
oups.com...
Hi All,

I have a list of excel files what I produce a summary from the
calculations if the same for each cell that I want to enter only the
file name changes, I have writted the below function:-


=CONCATENATE("=SUM('[",'File list'!A1,E2,"]Monthly Charts'!$S$4:$W$5)/
5")

Which gets me the text that I want

=SUM('[Accounts Comms Sheet.xls]Monthly Charts'!$S$4:$W$5)/5

but is displays it as a text entry instead of been used as a function,
how can I get it to do the sum?

Cheers
Mark