Help with MATCH function

P

Philip

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
 
V

vezerid

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
 
D

Dave Peterson

=indirect() will only work if the sending workbook is open.
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
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
Click to expand...
Click to expand...
 
P

Philip

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 said:
=indirect() will only work if the sending workbook is open.
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
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
Click to expand...
Click to expand...
Click to expand...
 
P

Philip

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

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

vezerid said:
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

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
Click to expand...
Click to expand...
 
V

vezerid

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

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

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

vezerid said:
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

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with SET & REF function 0
=MATCH with nested arrays assistance needed 0
VB Function for Copy & Paste 0
match function with nested if 12
Function or Sub to help with text string 5
Index Match to ignore blank cells 0
Match function: error due to format? 4
index-match function across different worksheets 0

Top