| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking sheets in a
We have a daily file with several worksheets....in one of the tabs I link
specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
This worked for me =INDIRECT(TEXT(TODAY(),"mmddyyyy")&"!L1192")
Please note that while not incorrect there is no need for the + sign in a formula; Lotus uses +A1 and Excel uses =A1. I am on a crusade to stop people saying "tab" when they mean "worksheet" or just "sheet". The tab is the do-hickey (tech term) you click to open a sheet. Sorry for the rant! But to use Help effectively one need to know the right terms. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "pm" wrote in message ... We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
=INDIRECT("'"&TEXT(TODAY(),"mmddyy")&"'!L1192")
[Note that you don't need a + in your formula if you are not adding anything. That looks like a relic from old Lotus spreadsheets.] -- David Biddulph "pm" wrote in message ... We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
=indirect("'" & text(today(),"mmddyy") & "'!L1192")
or maybe: =indirect("'" & text(today()-1,"mmddyy") & "'!L1192") If you wanted to use yesterday's date. pm wrote: We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
Hi Bernard,
I tried this and I'm getting #REF in the field? "Bernard Liengme" wrote: This worked for me =INDIRECT(TEXT(TODAY(),"mmddyyyy")&"!L1192") Please note that while not incorrect there is no need for the + sign in a formula; Lotus uses +A1 and Excel uses =A1. I am on a crusade to stop people saying "tab" when they mean "worksheet" or just "sheet". The tab is the do-hickey (tech term) you click to open a sheet. Sorry for the rant! But to use Help effectively one need to know the right terms. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "pm" wrote in message ... We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
Actually my error....it's not current date it's yesterdays date!!!
"Bernard Liengme" wrote: This worked for me =INDIRECT(TEXT(TODAY(),"mmddyyyy")&"!L1192") Please note that while not incorrect there is no need for the + sign in a formula; Lotus uses +A1 and Excel uses =A1. I am on a crusade to stop people saying "tab" when they mean "worksheet" or just "sheet". The tab is the do-hickey (tech term) you click to open a sheet. Sorry for the rant! But to use Help effectively one need to know the right terms. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "pm" wrote in message ... We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
Thank you very much...that works!!
"Dave Peterson" wrote: =indirect("'" & text(today(),"mmddyy") & "'!L1192") or maybe: =indirect("'" & text(today()-1,"mmddyy") & "'!L1192") If you wanted to use yesterday's date. pm wrote: We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
Sorry, one other thing. Is there an easy way to copy the formula down,
since the cell is in quotes? "Dave Peterson" wrote: =indirect("'" & text(today(),"mmddyy") & "'!L1192") or maybe: =indirect("'" & text(today()-1,"mmddyy") & "'!L1192") If you wanted to use yesterday's date. pm wrote: We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
What row is the formula going?
If it's going in row 1, then you could use: =indirect("'" & text(today()-1,"mmddyy") & "'!L" & row()+1191) row()+1191 will have to add to 1192. pm wrote: Sorry, one other thing. Is there an easy way to copy the formula down, since the cell is in quotes? "Dave Peterson" wrote: =indirect("'" & text(today(),"mmddyy") & "'!L1192") or maybe: =indirect("'" & text(today()-1,"mmddyy") & "'!L1192") If you wanted to use yesterday's date. pm wrote: We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
Why bother with the single quotes?
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dave Peterson" wrote in message ... =indirect("'" & text(today(),"mmddyy") & "'!L1192") or maybe: =indirect("'" & text(today()-1,"mmddyy") & "'!L1192") If you wanted to use yesterday's date. pm wrote: We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Linking sheets in a
They don't hurt if they're not needed, but if the OP (or any lurker) wants to
modify the formula, it'll still work with no problems. More of a pedagogical choice than a requirement in this case. Bernard Liengme wrote: Why bother with the single quotes? -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dave Peterson" wrote in message ... =indirect("'" & text(today(),"mmddyy") & "'!L1192") or maybe: =indirect("'" & text(today()-1,"mmddyy") & "'!L1192") If you wanted to use yesterday's date. pm wrote: We have a daily file with several worksheets....in one of the tabs I link specific fields to another tab that uses the current date as the name - =+'022808'!L1192 ......so the tab name changes daily to reflect todays date. My question is, how can I link to a tab that has a different name every day? Is there a formula that I can use instead of +'031008'!L1192? -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking a name across 30 sheets. | Excel Worksheet Functions | |||
| Linking Sheets | Links and Linking in Excel | |||
| Linking between sheets? | Excel Discussion (Misc queries) | |||
| Need Help Linking Sheets | Excel Discussion (Misc queries) | |||
| linking sheets | Excel Discussion (Misc queries) | |||
Linear Mode
