Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index function with quotes and '&' sign in reference section
I am trying to use a changing reference inside an 'index' function,
however I keep getting a '#value' error. In trying to troubleshoot, I simplified the formula down to this: =INDEX('Jan 07'!$C$22&":$C$24",1) I still get the '#value' error. Ultimately, I would like to do this: =INDEX("'X:\Path\[Filename.xls]worksheettab'! $"&substitute(address(1,10,4),"1","")&"$527"&":$J$ 563",1) But I think I need to take baby steps. :) The 'substitute(address)' part of the formula is just to get a column letter to use with the number $527 to create a cell reference. If there is a simpler way to do this don't hestitate to tell me. :) Also, I am trying to avoid using the 'indirect' function. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index function with quotes and '&' sign in reference section
Linking to closed workbooks isn't supported - so you can't dynamically build
your reference. If you want something like this, use the change event or the calculate event to hard code the formula in the cell (building the appropriate link). If the workbook is open, then you can use the Indirect function to build the reference. -- Regards, Tom Ogilvy wrote in message ups.com... I am trying to use a changing reference inside an 'index' function, however I keep getting a '#value' error. In trying to troubleshoot, I simplified the formula down to this: =INDEX('Jan 07'!$C$22&":$C$24",1) I still get the '#value' error. Ultimately, I would like to do this: =INDEX("'X:\Path\[Filename.xls]worksheettab'! $"&substitute(address(1,10,4),"1","")&"$527"&":$J$ 563",1) But I think I need to take baby steps. :) The 'substitute(address)' part of the formula is just to get a column letter to use with the number $527 to create a cell reference. If there is a simpler way to do this don't hestitate to tell me. :) Also, I am trying to avoid using the 'indirect' function. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index function with quotes and '&' sign in reference section
So what does 'Jan 07'!$C$22 contain.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... I am trying to use a changing reference inside an 'index' function, however I keep getting a '#value' error. In trying to troubleshoot, I simplified the formula down to this: =INDEX('Jan 07'!$C$22&":$C$24",1) I still get the '#value' error. Ultimately, I would like to do this: =INDEX("'X:\Path\[Filename.xls]worksheettab'! $"&substitute(address(1,10,4),"1","")&"$527"&":$J$ 563",1) But I think I need to take baby steps. :) The 'substitute(address)' part of the formula is just to get a column letter to use with the number $527 to create a cell reference. If there is a simpler way to do this don't hestitate to tell me. :) Also, I am trying to avoid using the 'indirect' function. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index function with quotes and '&' sign in reference | Excel Worksheet Functions | |||
INDEX function need to have col reference to be formula | Excel Worksheet Functions | |||
Now With Index -- IF Function Does Not Work With Cell Reference | Excel Worksheet Functions | |||
Obtaining cell reference from index function | Excel Programming | |||
Cell reference from previous index function | Excel Programming |