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