update vlookup formula sheet reference for multiple sheets

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

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:P"),13,false)
and drag to the right and then drag down the data???

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

--

Dave Peterson

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)

Whe

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 A1:P200, 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

"SRH@Boise" wrote in message
...
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

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

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)

Whe

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 A1:P200, 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

"SRH@Boise" wrote in message
...
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

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