Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify formula
Look in HELP for the INDIRECT() function
-- Kind Regards, Niek Otten Microsoft MVP - Excel "ParTeeGolfer" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify formula
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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify a Formula | Excel Worksheet Functions | |||
Modify a Formula | Excel Worksheet Functions | |||
Modify a formula? | Excel Discussion (Misc queries) | |||
Modify a Formula | Excel Worksheet Functions | |||
Modify A formula | Excel Worksheet Functions |