modify formula

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!

Look in HELP for the INDIRECT() function

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"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!

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!