| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable For Worksheet Name
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable For Worksheet Name
Try
=VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]"&RIGHT(B47,2)&"!$B$2:$AO$100"),37,FALSE) -- HTH Bob Phillips "ParTeeGolfer" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable For Worksheet Name
Thanks Bob,
However this did not work. It seems that the workshhet did not even recognise this as a formula. Any other suggestions? "Bob Phillips" wrote: Try =VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]"&RIGHT(B47,2)&"!$B$2:$AO$100"),37,FALSE) -- HTH Bob Phillips "ParTeeGolfer" wrote in message ... 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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable For Worksheet Name
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 "ParTeeGolfer" wrote in message ... Thanks Bob, However this did not work. It seems that the workshhet did not even recognise this as a formula. Any other suggestions? "Bob Phillips" wrote: Try =VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]"&RIGHT(B47,2)&"!$B$2:$AO$100"),37,FALSE) -- HTH Bob Phillips "ParTeeGolfer" wrote in message ... 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable For Worksheet Name
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 "ParTeeGolfer" wrote in message ... Thanks Bob, However this did not work. It seems that the workshhet did not even recognise this as a formula. Any other suggestions? "Bob Phillips" wrote: Try =VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]"&RIGHT(B47,2)&"!$B$2:$AO$100"),37,FALSE) -- HTH Bob Phillips "ParTeeGolfer" wrote in message ... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Link variable data from one worksheet to another worksheet? | New Users to Excel | |||
| Variable worksheet name (tab) linked to cell in same worksheet | Excel Worksheet Functions | |||
| Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
| Variable worksheet name | Excel Worksheet Functions | |||
| Assigning worksheet to worksheet variable | Excel Programming | |||
Linear Mode
