From: SRH on
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
From: Dave Peterson on
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(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

--

Dave Peterson
From: L. Howard Kittle on
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 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(a)Boise" <SRHBoise(a)discussions.microsoft.com> wrote in message
news:7E170099-169D-49F7-893E-EC9388709F69(a)microsoft.com...
> 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


From: Jacob Skaria on
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
From: L. Howard Kittle on
<<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 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(a)Boise" <SRHBoise(a)discussions.microsoft.com> wrote in message
news:7E170099-169D-49F7-893E-EC9388709F69(a)microsoft.com...
> 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