A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

INDIRECT(ADDRESS) inside a VLOOKUP



 
 
Thread Tools Display Modes
  #11  
Old April 22nd, 2009, 06:20 PM posted to microsoft.public.excel.worksheet.functions
JG
external usenet poster
 
Posts: 75
Default 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  
Old April 22nd, 2009, 07:49 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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  
Old April 22nd, 2009, 08:01 PM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default 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  
Old April 22nd, 2009, 08:33 PM posted to microsoft.public.excel.worksheet.functions
JG
external usenet poster
 
Posts: 75
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.