Help with MATCH function
 

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

Help with MATCH function
 

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

Help with MATCH function
 

=indirect() will only work if the sending workbook is open.

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

--

Dave Peterson

Help with MATCH function
 

Hi,

Yes that's correct, so we're using the INDIRECT.EXT function from Laurent
Longre's Morefunc.xll

it works the same but with closed workbooks... until I can persuede
Microsoft to change it in Excel 2007 !

Philip

"Dave Peterson" wrote:

=indirect() will only work if the sending workbook is open.

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

--

Dave Peterson

Help with MATCH function
 

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

Help with MATCH function
 

If you want to use this formula in VBA code you can use the following:
1. Gradually build the string.
2. Use "" inside double quotes to express "

HTH
Kostis Vezerides


Philip wrote:
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