Linking sheets in a

[pm]

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?

 

[Bernard Liengme]

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

 

[David Biddulph]

=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.]

 

[Dave Peterson]

=indirect("'" & text(today(),"mmddyy") & "'!L1192")
or maybe:
=indirect("'" & text(today()-1,"mmddyy") & "'!L1192")
If you wanted to use yesterday's date.

 

[pm]

Hi Bernard,

I tried this and I'm getting #REF in the field?

 

[pm]

Actually my error....it's not current date it's yesterdays date!!!

 

[pm]

Thank you very much...that works!!

=indirect("'" & text(today(),"mmddyy") & "'!L1192")
or maybe:
=indirect("'" & text(today()-1,"mmddyy") & "'!L1192")
If you wanted to use yesterday's date.

 

[pm]

Sorry, one other thing. Is there an easy way to copy the formula down,
since the cell is in quotes?

 

[Dave Peterson]

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.

 

[Bernard Liengme]

Why bother with the single quotes?

 

[Dave Peterson]

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.