INDIRECT.EXT question

Never used it but try:

=INDIRECT.EXT("'C:\Users\WIN7\Desktop\auto inven\[Book2.xlsx]July 4, 2012'!" & C11)


Regards, TMS

I'm sorry; I don't have that tool and your example workbooks don't seem to use it either ... at least, not as far as I can see.

I've asked for others to have a look at the question.

Regards, TMS

You are dragging to the right? What should the next cell result be? D11?

If so, then maybe this:

=INDIRECT.EXT("'C:\Users\WIN7\Desktop\auto inven\[Book2.xlsx]July 4, 2012'!" & ADDRESS(11,COLUMN(C1)))

First issue of many is incrementing the row reference whilst dragging across columns. The automatic increment would be to increase the column reference, So D11 then E11, F11 etc.

Next problem is the columns are in the reverse order to your rows.
So Column B in Report.xls contains information for '5/8 But the row in Book2.xls is row 17 and not row 6. This means you actual want the row reference to start at 17 and decrease as you drag across.

Finally you have the issue of INDIRECT not working when the referred to work book is closed.
Edit: I assume INDIRECT.EXT get around this limitation?

You may have to use VLOOKUP or a combination of INDEX and MATCH to locate and reference the data. Either way you will need to standardised the descriptive text to get a match.
The you can use a named range to reference the workbook and data range. This would need to be changed when the file name changes but at least the modification will only be needed in 1 place.

Expand on Jerry's suggestion,

=ADDRESS(19-COLUMN(),2)