Variable For Worksheet Name

[Guest]

Iam using the following Formula:

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

Where "Game 47 " is the name of a workbook 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 "Foirmula contains an invalid reference", How can I get "Game *" to
be a variable without an error?

Thanks in advance!

 

[Bob Phillips]

Try

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

 

[Guest]

Thanks Bob,

However this did not work. It seems that the workshhet did not even
recognise this as a formula.

Any other suggestions?

Try

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


--
HTH

Bob Phillips

Iam using the following Formula:

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

Where "Game 47 " is the name of a workbook 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 "Foirmula contains an invalid reference", How can I get "Game *" to
be a variable without an error?

Thanks in advance!

 

[Bob Phillips]

The only thing I can think of is that it should use B47 not Right(B47,2)
which I just copied from your formula.

--
HTH

Bob Phillips

Thanks Bob,

However this did not work. It seems that the workshhet did not even
recognise this as a formula.

Any other suggestions?

Try

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


--
HTH

Bob Phillips

Iam using the following Formula:

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

Where "Game 47 " is the name of a workbook 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 "Foirmula contains an invalid reference", How can I get "Game

*"
to

be a variable without an error?

Thanks in advance!

 

[Tom Ogilvy]

Think Bob had a typo or two, but the advice was sound.
Assuming the Sheet name is GAME 37

and B47 contains something like ABCD37

this worked fine for me:

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

Copied out of a cell where it was working very well.

--
Regards,
Tom Ogilvy

Thanks Bob,

However this did not work. It seems that the workshhet did not even
recognise this as a formula.

Any other suggestions?

Try

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


--
HTH

Bob Phillips

Iam using the following Formula:

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

Where "Game 47 " is the name of a workbook 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 "Foirmula contains an invalid reference", How can I get "Game

*"
to

be a variable without an error?

Thanks in advance!