Dereferencing values for area in a closed workbook using Indirect.ext and then performing a vlookup

[Paul Liu]

In a past by Harlan Grove, he states that you can get around the lack
of range limitation in a closed excel workbook by putting the formula
in the following syntax:

=INDIRECT.EXT("'"&DirectoryPath&"["&WorkbookName&"]"&WorksheetName&"'!"&
CELL("Address",OFFSET(A1,row_offset,column_offset)))

He then states that you can deference values for multiple areas by
adding a second OFFSET statement. What is the exact syntax for doing
this? Can the user vlookup or sumif this dereferenced area?

Any help would be appreciated,

Paul

 

[Harlan Grove]

In a past by Harlan Grove, he states that you can get around the lack
of range limitation in a closed excel workbook by putting the formula
in the following syntax:

=INDIRECT.EXT("'"&DirectoryPath&"["&WorkbookName&"]"&WorksheetName&"'!"&
CELL("Address",OFFSET(A1,row_offset,column_offset)))

He then states that you can deference values for multiple areas by
adding a second OFFSET statement. What is the exact syntax for doing
this? Can the user vlookup or sumif this dereferenced area?

Let's be precise. What I wrote in

http://www.google.com/[email protected]

was, "If the OP wants to dereference the values from a single area, multiple
cell range in a closed workbook, two CELL("Address",OFFSET(...)) calls would be
needed." Note the phrase 'single area'. What I meant was something like

=INDIRECT.EXT("'"&DirectoryPath&"["&WorkbookName&"]"&WorksheetName&"'!"&
CELL("Address",OFFSET(A1,topleft_row_offset,topleft_col_offset))&":"&
CELL("Address",OFFSET(A1,bottomright_row_offset,bottomright_col_offset)))

though I've recently discovered a hack that requires a single CELL/OFFSET
expression, though at the cost of a MID call.

=INDIRECT.EXT("'"&DirectoryPath&"["&WorkbookName&"]"&WorksheetName&"'!"&
MID(CELL("Address",(A1,OFFSET(A1,row_offset,col_offset,num_rows,num_cols))),
6,256))

However, INDIRECT.EXT just doesn't work like this. I was wrong in that previous
posting, and Frank Kabel was right: INDIRECT.EXT can only return a single value.
You'll need to see the alternatives in the following.

http://www.google.com/[email protected]

Any function that accepts arrays instead of ranges can accept the results from
SQL.REQUEST or pull() [both mentioned in the preceding linked article], so
VLOOKUP should accept results from either. Any function that *requires* range
arguments only can't be used with references of any kind into closed workbooks,
so SUMIF *only* works with open workbooks.

 

[Frank Kabel]

Hi Harlan
[....]

though I've recently discovered a hack that requires a single
CELL/OFFSET expression, though at the cost of a MID call.

=INDIRECT.EXT("'"&DirectoryPath&"["&WorkbookName&"]"&WorksheetName&"'!"
&MID(CELL("Address",(A1,OFFSET(A1,row_offset,col_offset,num_rows,num_col
s))),
6,256))

very neat!

However, INDIRECT.EXT just doesn't work like this. I was wrong in
that previous posting, and Frank Kabel was right: INDIRECT.EXT can
only return a single value. You'll need to see the alternatives in
the following.

So I'll mark three crosses in my calendar for this day: This was my
easter present after all. I was right and Harlan wasn't
Though i had to admit I didn't spot it on my own but this is so rare I
couldn't resist...

Wish you a nice evening
Frank