no, it seems I am stuck building the formula using code in the event...

There's always some problem with the quotes...

"vezerid" wrote:

Does the following work? Sorry, could not test.

=MATCH("Basis B",
INDIRECT("'"&MasterPath&"\"&ExportDate&"\[Export"&INDIRECT(ADDRESS(SUM(ROW()),2))&".xls]Export'!$E$7:$Z$7",0)

HTH
Kostis Vezerides


Philip wrote:
Hi,

I have a worksheet function (in fact hundreds!) like this:

=MATCH("Basis B", 'C:\SRCPath\20061017\[ExportFUND.xls]Export'!$E$7:$Z$7,0)

This returns the column that another cell uses in an ADDRESS function with
INDIRECT to return a value from another workbook.

Basis B comes from the column Header
C:\SRCPath\ is the same as a cell called 'MasterPath' - but is static
20061017 comes from a cell called 'ExportDate'
FUND comes from column B on the same row as the function

So, is there any way to derive the path dynamically, in other words, when
the cell called 'ExportDate' changes, or when the cell called 'MasterPath'
changes, currently I have code running in the Worksheet_Change event to
update all the formulae with the new Path and the date ...

I have tried to do something like this:

MasterPath&"[Export"&B17&".xls]Export'!"

then tack on the search range at the end so it looks like this

="'"&MasterPath&"[Export"&B17&".xls]Export'!$E$7:$Z$7"

but when I put that into my final formula:
=MATCH("Basis B","'"&MasterPath&"[Export"&B16&".xls]Export'!$E$7:$Z$7",0)

it always fails to evaluate and I get a #VALUE error.

So, that's the challenge - is there anyway to derive the oath to the MATCH
function from other cells like that?

I works fine using the macro in the change event of course, but if possible
I'd prefer to have a worksheet function managing to workout the path itself!

Thanks for any help

Philip