Convert text to formula - Help pleaaase
Hi, I have a formula such as "=sum(Hello!A1:B2)", where "Hello" is a spreadsheet. I have a column with all the names of my spredsheets and I need to use the same set of formula referencing to all of them, one by one. I need to, either: Replace the name of the preadsheet by a cell reference so I can use the same fomula to call various spreadsheet. or find a way to convert a text into a formula - to do the above, I can use the "SUBSTITUTE" function into my formula but this is then a text and I cannot find the way to evaluate it (The INDIRECT function does not work...) Can anybody help please??????? -- Ayrton ------------------------------------------------------------------------ Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438 View this thread: http://www.excelforum.com/showthread...hreadid=491471 |
Convert text to formula - Help pleaaase
INDIRECT does work as long as the workbook with the sheets in question is
open =SUM(INDIRECT("'"&A1&"'!A1:B2")) with the sheet name in A1 -- Regards, Peo Sjoblom "Ayrton" wrote in message ... Hi, I have a formula such as "=sum(Hello!A1:B2)", where "Hello" is a spreadsheet. I have a column with all the names of my spredsheets and I need to use the same set of formula referencing to all of them, one by one. I need to, either: Replace the name of the preadsheet by a cell reference so I can use the same fomula to call various spreadsheet. or find a way to convert a text into a formula - to do the above, I can use the "SUBSTITUTE" function into my formula but this is then a text and I cannot find the way to evaluate it (The INDIRECT function does not work...) Can anybody help please??????? -- Ayrton ------------------------------------------------------------------------ Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438 View this thread: http://www.excelforum.com/showthread...hreadid=491471 |
Convert text to formula - Help pleaaase
Thanks a lot. your "grammar" does work but I am not sure why the "'" ... are for? I cannot find this example in the help... Anyways, thanks again -- Ayrton ------------------------------------------------------------------------ Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438 View this thread: http://www.excelforum.com/showthread...hreadid=491471 |
Convert text to formula - Help pleaaase
The "'" at the start is a way of making sure it will work if you have sheet
names with spaces in them since excel automatically precede those with an apostrophe (I assume you don't have sheet named Hello) by adding this you can use whatever sheet name you want. The last pair "'!A1:B2" is because INDIRECT needs them to make that string converted to a usable value. e.g. =INDIRECT(A1) will not return what's in A1, INDIRECT("A1") will If A1 holds a reference like A2 then =INDIRECT(A1) will return what's in A2 -- Regards, Peo Sjoblom "Ayrton" wrote in message ... Thanks a lot. your "grammar" does work but I am not sure why the "'" ... are for? I cannot find this example in the help... Anyways, thanks again -- Ayrton ------------------------------------------------------------------------ Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438 View this thread: http://www.excelforum.com/showthread...hreadid=491471 |
Convert text to formula - Help pleaaase
Ayrton
The "'" places ' around the sheet name. This is rquired if the sheet name has space(s) in it. ='sheet one'!A16 requires the ' ' around the name. =Sheet2!A13 does not require ' ' Gord Dibben Excel MVP On Wed, 7 Dec 2005 09:34:42 -0600, Ayrton wrote: Thanks a lot. your "grammar" does work but I am not sure why the "'" ... are for? I cannot find this example in the help... Anyways, thanks again |
Convert text to formula - Help pleaaase
Thanks again -- Ayrton ------------------------------------------------------------------------ Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438 View this thread: http://www.excelforum.com/showthread...hreadid=491471 |
All times are GMT +1. The time now is 01:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com