INDIRECT.EXT / MATCH / ADDRESS Functions Madness
Philip
I posted a question about using INDIRECT and ADDRESS / MATCH on closed
workbooks, to return values when you don't know which column the data is in
(see "Advanced Worksheet Formulas ... :") and I got lots of help.
Now, I am using INDIRECT.EXT as the workbooks containing the data are
closed. I am using MATCH to find teh correct row and column of the data I
want to return (given that I cannot hardcode the column). I am using ADDRESS
to return all that to the INDIRECT.EXT function as a correct cell range.
I have a hardcoded path in the formula at the moment, and it works fine:
=INDIRECT.EXT(CONCATENATE(D3,ADDRESS(MATCH($A3,'T:\Excel\ABM Amro
Expenses\Expenses\NAV ALERT\EXPORT
ARCHIVE\TCF\20040816\[ExportIK05.xls]Export'!$A$9:$A$93,0)+8,MATCH(H$1,'T:\Excel\ABM
Amro Expenses\Expenses\NAV ALERT\EXPORT
ARCHIVE\TCF\20040816\[ExportIK05.xls]Export'!$E$7:$Z$7,0)+4,1,TRUE)))
The path you can see is defined in the following way:
1) Drive and main folder for Funds = T:\Excel\ABM Amro Expenses\Expenses\NAV
ALERT\EXPORT ARCHIVE\TCF\
2) Date formatted to yyyymmdd = 20040816
3) Workbook name ( "Export" and Fund & .xls ) = ExportIK05.xls
Now, I want to not hardcode the date (20040816), the fund name (Ik05), or
the path (T:\Excel\ABM Amro Expenses\Expenses\NAV ALERT\EXPORT ARCHIVE\TCF\)
I need to use a value called "Current NAV", so basically we search in rows
A9:A93 for the name of the value we want which is equal to the Cell A3 !) .
And I need to get the column without knowing beforehand what column it is on
the source sheet (ExportIK05.xls)... so I use MATCH in cells E7:Z7 against
the value in H1 that is the column heading and the name of the class!
So, how can I concatenate the MATCH parameters (say "$A$9:$A$93") with the
path that is built by functions as described, and output the whole lot as an
ADDRESS for the INDIRECT.EXT special worksheet function.
BTW, "INDIRECT.EXT" is from the excellent Add-In library at
http://longre.free.fr/english/ and is identical to INDIRECT except it works
on closed workbooks...
thanks for any help or assistance...
Philip