Perhaps you could try using INDIRECT()
to reference the table array where the sheetname is partly a variable

Something along these lines should work (both untested, apologies):

=VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]Game
"&(RIGHT(B47,2))&"'!$B$2:$AO$100"),37,FALSE)

=VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]Game
"&AP47&"'!$B$2:$AO$100),37,FALSE)

Note that INDIRECT requires the source book (2004 Rush Offensive Stats.xls)
to be open, otherwise you'll get #REF!

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"ParTeeGolfer" wrote:

Iam using the following Formula:

=VLOOKUP($W$5,'[2004 Rush Offensive Stats.xls]Game 47'!$B$2:$AO$100,37,FALSE)

Where "Game 47 " is the name of a worksheet in a specific workbook, I would
like to have the number "47" as a variable.

If I try:
=VLOOKUP($W$5,'[2004 Rush Offensive Stats.xls]Game
(RIGHT(B47,2))'!$B$2:$AO$100,37,FALSE)

OR

=VLOOKUP($W$5,'[2004 Rush Offensive Stats.xls]Game
&AP47'!$B$2:$AO$100,37,FALSE)

I get a "Formula contains an invalid reference", How can I get "Game *" to
be a variable without an error?

The cell content of B47 is in another workbook called "2004 Individulal
Stats"which is where I would like to get part of the information needed to
complete the above formula.

Please help on how to accomplish this task

Thanks in advance!