When you copy the formula down if the sheets names are not available it would
return a REF# error. To handle that use ISREF() and IF() as below

=IF(ISREF(INDIRECT("'"& TEXT($E$1+((ROW(A1)-1)*7),"m-d") & " to " &
TEXT($E$1+6+((ROW(A1)-1)*7),"m-d") &"'!a1")),VLOOKUP($A$1,
INDIRECT("'" & TEXT($E$1+((ROW(A1)-1)*7),"m-d") & " to " &
TEXT($E$1+6+((ROW(A1)-1)*7),"m-d") & "'!$A:$P"),13,0),"")

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Try the below formula

You need to have the start date in a separate cell. In the below formula
cell E1 holds the start date which is 4/4/2010 in excel date format.The below
formula would build the sheets names as shown below....

=TEXT($E$1+((ROW(A1)-1)*7),"m-d") & " to " &
TEXT($E$1+6+((ROW(A1)-1)*7),"m-d")

4-4 to 4-10
4-11 to 4-17
4-18 to 4-24
4-25 to 5-1
5-2 to 5-8
5-9 to 5-15

The below vlookup formula use the above indirect() formula to build the
sheet name..

=VLOOKUP($A$1,INDIRECT("'" & TEXT($E$1+((ROW(A1)-1)*7),"m-d") &
" to " & TEXT($E$1+6+((ROW(A1)-1)*7),"m-d") & "'!$A:$P"),13,0)


--
Jacob (MVP - Excel)


"SRH@Boise" wrote:

In Excel 2003
Starting with this formula I need to have the sheet name change to each tab
available on the sheet.
=VLOOKUP($A$1,'4-4 to 4-10'!$A:$P,13,FALSE)
Other tab names a
4-11 to 4-17
4-18 to 4-24
4-25 to 5-1

Looking for a more automated way to create the following other than manually
change the sheet name.
=VLOOKUP($A$1,'4-11 to 4-17'!$A:$P,13,FALSE)
=VLOOKUP($A$1,'4-18 to 4-24!$A:$P,13,FALSE)

I think I am seeing a possibility to use INDIRECT but not sure how. Or is
this VBA stuff?

--
SRH