CONCATENATE to make a formula

[Sh0t2bts]

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

 

[Peo Sjoblom]

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/

 

[Sh0t2bts]

Thanks you for the fast reply )

 

[David Biddulph]

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

 

[Bernard Liengme]

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

 

[PCLIVE]

This would be my guess using INDIRECT.

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

HTH,
Paul