update vlookup formula sheet reference for multiple sheets

[SRH@Boise]

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?

 

[Dave Peterson]

I'm not quite sure what you're doing, but maybe you could insert a new row 1.

Then put the worksheet names in B1, C1, D1, ...

Then you could use a formula like:
=vlookup($a$2,indirect("'" & b$1 & "'!A"),13,false)
and drag to the right and then drag down the data???

 

[L. Howard Kittle]

Hi SRH @ spudville,

I think this rather complicated vlookup formula I got from Peo Sjoblom will
do what you want.

You enter a value in the lookup value cell and this formula will look across
many sheet tabs in the workbook. (I note that you said "...each tab
available on the sheet" and I assume you mean on the workbook.)

It looks formitable but is not too bad to walk through and adapt to your
workbook. I am able to work with it but cannot explain to any satisfaction
why it works, even with Peo's four page e-mail on how it works.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)>0),0))&"'!A2:C200"),3,0)

Where:

A1 is the lookup value on the sheet holding the formula.

MySheets is a named range of all the sheets you want to conduct the
lookup. Make a list of the sheet names, select them and name it.

A2:A200 are the columns on EACH sheet and is the Table_Array which you want
to look up. (Yours might look like A1200, which is 16 columns and you
want to return the 13th column.)

You will need to change the third argument of 3 in the sample to 13 for your
use to return the 13th column.

You must Array Enter the formula uaing CTRL+SHIFT+ENTER.

If you make changes to the formula you will again have to use
Ctrl+Shift+Enter to commit.

Problems...? Post back.

HTH
Regards,
Howard

 

[Jacob Skaria]

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)

 

[L. Howard Kittle]

<<This is a repost, first one did not seem to show up>>

Hi SRH @ spudville,

I think this rather complicated vlookup formula I got from Peo Sjoblom will
do what you want.

You enter a value in the lookup value cell and this formula will look across
many sheet tabs in the workbook. (I note that you said "...each tab
available on the sheet" and I assume you mean on the workbook.)

It looks formitable but is not too bad to walk through and adapt to your
workbook. I am able to work with it but cannot explain to any satisfaction
why it works, even with Peo's four page e-mail on how it works.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)>0),0))&"'!A2:C200"),3,0)

Where:

A1 is the lookup value on the sheet holding the formula.

MySheets is a named range of all the sheets you want to conduct the
lookup. Make a list of the sheet names, select them and name it.

A2:A200 are the columns on EACH sheet and is the Table_Array which you want
to look up. (Yours might look like A1200, which is 16 columns and you
want to return the 13th column.)

You will need to change the third argument of 3 in the sample to 13 for your
use to return the 13th column.

You must Array Enter the formula uaing CTRL+SHIFT+ENTER.

If you make changes to the formula you will again have to use
Ctrl+Shift+Enter to commit.

Problems...? Post back.

HTH
Regards,
Howard

 

[Jacob Skaria]

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),"")