modify formula

[Guest]

Ihave the following formula which does work, however I would like to modify
so I can insert lines and not have to retype portions of this formula.

=IF($A47="","",VLOOKUP($W$5,'C:\Travel Basketball\[2004 Rush Offensive
Stats.xls]Game 38'!$B$2:$AH$17,11,FALSE))

I would like to have somethink like:

=IF($A47="","",VLOOKUP($W$5,'C:\Travel Basketball\[2004 Rush Offensive
Stats.xls]$B47!$B$2:$AH$17,11,FALSE))

but I get an error with this formula

$B47 is in the workbook that I am currently working on (or opened) and Rush
Offensive Stats is not opened. I know this can be done with an indirect
command however I am trying to prevent the indirect.

With the top formula, Rush Offensive Stats does not have to be opened
however the worksheet is named directly(GAME 38)

I need to have this as a variable

PLEASE HELP!

 

[Niek Otten]

Look in HELP for the INDIRECT() function

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Tom Ogilvy]

Indirect doesn't work with closed workbooks.

there isn't any built in alternative.

You could probably use a change event to enter the formula (hard coded) in
the cell if you give the current B47 a name (insert name define) so you can
maintain where it moves when you make changes.