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.

--
Regards,
Tom Ogilvy

"ParTeeGolfer" <ParTeeGolfer@discussions.microsoft.com> wrote in message
news:59DA7F26-7404-48E0-BA3C-293F96D6267A@microsoft.com...
> 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!