Prev: Question using countif
Next: Colour code specific name if it appears in a group of names
From: Jacob Skaria on 8 May 2010 03:51
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(a)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 are:
> > 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
First  |  Prev  | 
Pages: 1 2
Prev: Question using countif
Next: Colour code specific name if it appears in a group of names