| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing worksheet name within a formula (automatically)
Hello please help,
I have a summary worksheet with a formula taking data from another worksheet. Simply ='worksheet'!G34. At certain times of year I have to change the worksheet name to equal the current period in all of the cells in the summary sheet. I have worksheets called "promotional detail LE01 2007" all the way through to LE12. I have in the summary sheet as a header LE01 which changes by a cover sheet. Is there a way I can have the formula so it changes the worksheet name to correspond with the header. E.G cell A1 = LE01 cell A2 = 2007 formula ='Promotional Detail LE09 2007'!G34 Is there a way so that the end of the worksheet name can equal cell a1 and a1. I have tried the following but it is not working please help. =INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34") Many thanks in advance |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing worksheet name within a formula (automatically)
It looks like you are missing a single quote at the beginning of the
worksheet name =INDIRECT("'Promotional Detail "&A1&" "&A2&"'!G34") "dave" wrote: Hello please help, I have a summary worksheet with a formula taking data from another worksheet. Simply ='worksheet'!G34. At certain times of year I have to change the worksheet name to equal the current period in all of the cells in the summary sheet. I have worksheets called "promotional detail LE01 2007" all the way through to LE12. I have in the summary sheet as a header LE01 which changes by a cover sheet. Is there a way I can have the formula so it changes the worksheet name to correspond with the header. E.G cell A1 = LE01 cell A2 = 2007 formula ='Promotional Detail LE09 2007'!G34 Is there a way so that the end of the worksheet name can equal cell a1 and a1. I have tried the following but it is not working please help. =INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34") Many thanks in advance |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing worksheet name within a formula (automatically)
Your formula worked fine for me.
Are you sure you have the names of the sheets correct (maybe a trailing space--or multiple internal spaces)??? If you're sure you have it right, what does not working mean? dave wrote: Hello please help, I have a summary worksheet with a formula taking data from another worksheet. Simply ='worksheet'!G34. At certain times of year I have to change the worksheet name to equal the current period in all of the cells in the summary sheet. I have worksheets called "promotional detail LE01 2007" all the way through to LE12. I have in the summary sheet as a header LE01 which changes by a cover sheet. Is there a way I can have the formula so it changes the worksheet name to correspond with the header. E.G cell A1 = LE01 cell A2 = 2007 formula ='Promotional Detail LE09 2007'!G34 Is there a way so that the end of the worksheet name can equal cell a1 and a1. I have tried the following but it is not working please help. =INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34") Many thanks in advance -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing worksheet name within a formula (automatically)
Try
=INDIRECT("'Promotional Detail"&" "&A1&" "&B1&"'!G34") Before the P we have " then ' Before the G we have " then ' followed by ! best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "dave" wrote in message ... Hello please help, I have a summary worksheet with a formula taking data from another worksheet. Simply ='worksheet'!G34. At certain times of year I have to change the worksheet name to equal the current period in all of the cells in the summary sheet. I have worksheets called "promotional detail LE01 2007" all the way through to LE12. I have in the summary sheet as a header LE01 which changes by a cover sheet. Is there a way I can have the formula so it changes the worksheet name to correspond with the header. E.G cell A1 = LE01 cell A2 = 2007 formula ='Promotional Detail LE09 2007'!G34 Is there a way so that the end of the worksheet name can equal cell a1 and a1. I have tried the following but it is not working please help. =INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34") Many thanks in advance |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing worksheet name within a formula (automatically)
It works brilliant thank you very much for your help extremely useful.
Thanks again Dave "Dave Peterson" wrote: Your formula worked fine for me. Are you sure you have the names of the sheets correct (maybe a trailing space--or multiple internal spaces)??? If you're sure you have it right, what does not working mean? dave wrote: Hello please help, I have a summary worksheet with a formula taking data from another worksheet. Simply ='worksheet'!G34. At certain times of year I have to change the worksheet name to equal the current period in all of the cells in the summary sheet. I have worksheets called "promotional detail LE01 2007" all the way through to LE12. I have in the summary sheet as a header LE01 which changes by a cover sheet. Is there a way I can have the formula so it changes the worksheet name to correspond with the header. E.G cell A1 = LE01 cell A2 = 2007 formula ='Promotional Detail LE09 2007'!G34 Is there a way so that the end of the worksheet name can equal cell a1 and a1. I have tried the following but it is not working please help. =INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34") Many thanks in advance -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Changing worksheet name within a formula (automatically)
I think you meant this note for Joel and Bernard. They both caught the missing
leading apostrophe. But I bet that they're both glad you got it working. dave wrote: It works brilliant thank you very much for your help extremely useful. Thanks again Dave "Dave Peterson" wrote: Your formula worked fine for me. Are you sure you have the names of the sheets correct (maybe a trailing space--or multiple internal spaces)??? If you're sure you have it right, what does not working mean? dave wrote: Hello please help, I have a summary worksheet with a formula taking data from another worksheet. Simply ='worksheet'!G34. At certain times of year I have to change the worksheet name to equal the current period in all of the cells in the summary sheet. I have worksheets called "promotional detail LE01 2007" all the way through to LE12. I have in the summary sheet as a header LE01 which changes by a cover sheet. Is there a way I can have the formula so it changes the worksheet name to correspond with the header. E.G cell A1 = LE01 cell A2 = 2007 formula ='Promotional Detail LE09 2007'!G34 Is there a way so that the end of the worksheet name can equal cell a1 and a1. I have tried the following but it is not working please help. =INDIRECT("Promotional Detail "&A1&" "&A2&"'!G34") Many thanks in advance -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically changing Year | Excel Discussion (Misc queries) | |||
| Automatically changing link. How? | Excel Discussion (Misc queries) | |||
| Formula automatically changing cell references | Excel Worksheet Functions | |||
| Changing a Worksheet Reference Formula using a drop down box | Excel Discussion (Misc queries) | |||
| Can inserted rows automatically include existing worksheet formula | Excel Discussion (Misc queries) | |||
Linear Mode
