workshhet variable

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!

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 <at>yahoo<dot>com
----
"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!

Is There an alternative without INDIREC?


"Max" wrote:

> 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 <at>yahoo<dot>com
> ----
> "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!

"ParTeeGolfer" wrote:
> Is There an alternative without INDIRECT?

IMO, INDIRECT's functionality offers the most straight-forward way
to string up the concatenated bits and get it working,
albeit the "slave" file(s) need to be open for it to work

Do hang around awhile for possibly better insights from others

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Clarification:
> 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)

The above suggestion presumes that "RIGHT(B47,2)" will evaluate to a number
like: 47

(Think there was an additional parens around "RIGHT(B47,2)" which was not
necessary - missed out earlier. This can be removed.)

If however, as per line in the original post:
> > The cell content of B47 is in another workbook called "2004 Individual
> > Stats" [corrected for typo] ...

then we might need another INDIRECT inside RIGHT(...),

For example, if you have in A2: B47
then you could out in say, B2:
=VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]Game
"&RIGHT(INDIRECT("'[2004 Individual
Stats.xls]Sheet1'!"&A2),2)&"'!$B$2:$AO$100"),37,FALSE)

where cell B47 in Sheet1 in book: 2004 Individual Stats.xls
contains say: 447, or T47

RIGHT(INDIRECT("'[2004 Individual Stats.xls]Sheet1'!"&A2),2)
will then evaluate to: 47

and the formula for the table array part:

INDIRECT("'[2004 Rush Offensive Stats.xls]Game "&RIGHT(INDIRECT("'[2004
Individual Stats.xls]Sheet1'!"&A2),2)&"'!$B$2:$AO$100")

will resolve to:

'[2004 Rush Offensive Stats.xls]Game 47'!$B$2:$AO$100

which is the table array in your original formula:
=VLOOKUP($W$5,'[2004 Rush Offensive Stats.xls]Game 47'!$B$2:$AO$100,37,FALSE)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----