If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
INDIRECT(ADDRESS) inside a VLOOKUP
F2/Enter didn't work, neither did recalc....
To your formula ideas below, both of those options worked. With the file open. But neither worked when the target was closed. "Dave Peterson" wrote: I think you're seeing that the formula hasn't reevaluated. With the sending workbook closed, select that cell with the =indirect() formula and hit F2 followed by enter. What happens? =========== I don't use =indirect.ext(), but maybe... =VLOOKUP(INDIRECT("RC1", FALSE), INDIRECT.EXT(("'\\server\share\folder\[Target File.xls]TCMetadata'!A2:G" & INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!A2")), 7) (Untested.) (I hardcoded the G into the formula instead of using =address(..., 7). And I dropped the $ signs from the strings (since it's in a string, it won't matter). One suggestion... If the used range of that sending worksheet isn't too much (you'll have to test it), maybe you can drop the count_tc stuff and just use the entire column. =VLOOKUP(INDIRECT("RC1", FALSE), INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!A:G"), 7) Dave Peterson |
#12
|
|||
|
|||
INDIRECT(ADDRESS) inside a VLOOKUP
Sorry, I don't use =indirect.ext() enough to help debug your problem.
Maybe someone else will jump in. JG wrote: F2/Enter didn't work, neither did recalc.... To your formula ideas below, both of those options worked. With the file open. But neither worked when the target was closed. "Dave Peterson" wrote: I think you're seeing that the formula hasn't reevaluated. With the sending workbook closed, select that cell with the =indirect() formula and hit F2 followed by enter. What happens? =========== I don't use =indirect.ext(), but maybe... =VLOOKUP(INDIRECT("RC1", FALSE), INDIRECT.EXT(("'\\server\share\folder\[Target File.xls]TCMetadata'!A2:G" & INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!A2")), 7) (Untested.) (I hardcoded the G into the formula instead of using =address(..., 7). And I dropped the $ signs from the strings (since it's in a string, it won't matter). One suggestion... If the used range of that sending worksheet isn't too much (you'll have to test it), maybe you can drop the count_tc stuff and just use the entire column. =VLOOKUP(INDIRECT("RC1", FALSE), INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!A:G"), 7) Dave Peterson -- Dave Peterson |
#13
|
|||
|
|||
INDIRECT(ADDRESS) inside a VLOOKUP
JG wrote...
.... =VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)), INDIRECT("[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2) .... Note: the external reference above needs to start off as either '[Filename.xls]MainQueryResult'!$B$2: or [Filename.xls]MainQueryResult!$B$2: but not as you show it above. IOW, the single quotes need to enclose the filename portion of the reference as well as the worksheet name, or you need to dispense with them. Wrapping only the worksheet names in single quotes is a reference syntax error, so Excel would CORRECTLY return #REF!. This isn't the best way to handle a problem like this. There's no good reason to use the first INDIRECT call. If you enter this formula in cell X99, you'd be better off replacing the first INDIRECT call with $A99. If you need to protect against range insertion/deletion moving cells in column A, replace the first INDIRECT call with INDEX($A:$A,SUM (ROW())). [SUM(ROW()) is intentional and protects against very subtle bugs that can arise because ROW always returns an array, even single results as single value arrays, and that can cause problems. Wrapping ROW() inside SUM converts the result to a scalar value.] Since you're using a sorted lookup, you could check whether your lookup value from col A would be found by the Count_Comp!A2 row of the external table, and only if so perform a lookup. Otherwise, just return the value at the Count_Comp!A2 row. =INDEX('[Filename.xls]MainQueryResult'!$B$2:$D$65536, IF(INDEX($A:$A,SUM(ROW()))INDEX('[Filename.xls]MainQueryResult'!$B $2:$B$65536,Count_Comp!$A$2), MATCH(INDEX($A:$A,SUM(ROW())),'[Filename.xls]MainQueryResult'!$B$2:$B $65536),Count_Comp!$A$2)) This should recalc more quickly. It also avoids volatile functions. |
#14
|
|||
|
|||
INDIRECT(ADDRESS) inside a VLOOKUP
Thanks, I appreciate the time.
"Dave Peterson" wrote: Sorry, I don't use =indirect.ext() enough to help debug your problem. Maybe someone else will jump in. |
|
Thread Tools | |
Display Modes | |
|
|