Using MATCH with parameters from functions...

G

Guest

Hi,

I have this to find the row of data in another workbook:

=MATCH(A5,'T:\Excel\ABM Amro Expenses\Expenses\NAV ALERT\EXPORT
ARCHIVE\SICAV\20040816\[ExportXXXX.xls]Export'!$A$9:$A$93,0)

where the XXXX is a fund like IK05 or MMAF...

What I need is something like this:

=MATCH(A5,"'" & DirectoryPath & DateFolder & "[Export" & Fund &
".xls]Export'!" & "$A$9:$A$93",0)

Is that possible, and how can I do this?

thanks for any adviece or assistance

Philip
 
G

Govind

Hi,

You can try

=MATCH(A5,INDIRECT("'" & DirectoryPath & DateFolder & "[Export" & Fund
&".xls]Export'!" & "$A$9:$A$93"),0)

Regards

Govind.
 
F

Frank Kabel

Hi Govibd

this will only work if the other file is open as INDIRECT
won't work on closed files :)
-----Original Message-----
Hi,

You can try

=MATCH(A5,INDIRECT("'" & DirectoryPath & DateFolder & "[Export" & Fund
&".xls]Export'!" & "$A$9:$A$93"),0)

Regards

Govind.


Hi,

I have this to find the row of data in another workbook:

=MATCH(A5,'T:\Excel\ABM Amro Expenses\Expenses\NAV ALERT\EXPORT
ARCHIVE\SICAV\20040816\[ExportXXXX.xls]Export'! $A$9:$A$93,0)

where the XXXX is a fund like IK05 or MMAF...

What I need is something like this:

=MATCH(A5,"'" & DirectoryPath & DateFolder & "[Export" & Fund &
".xls]Export'!" & "$A$9:$A$93",0)

Is that possible, and how can I do this?

thanks for any adviece or assistance

Philip
Click to expand...

.
Click to expand...
 
G

Govind

Hi Frank,

Thanks for the tip.

Any other formula suggested for this issue?

Regards

Govind.

Frank said:
Hi Govibd

this will only work if the other file is open as INDIRECT
won't work on closed files :)

-----Original Message-----
Hi,

You can try

=MATCH(A5,INDIRECT("'" & DirectoryPath & DateFolder
Click to expand...

& "[Export" & Fund
&".xls]Export'!" & "$A$9:$A$93"),0)

Regards

Govind.


Hi,

I have this to find the row of data in another workbook:

=MATCH(A5,'T:\Excel\ABM Amro Expenses\Expenses\NAV
Click to expand...
ALERT\EXPORT
ARCHIVE\SICAV\20040816\[ExportXXXX.xls]Export'!
Click to expand...
Click to expand...

$A$9:$A$93,0)
where the XXXX is a fund like IK05 or MMAF...

What I need is something like this:

=MATCH(A5,"'" & DirectoryPath & DateFolder & "[Export"
Click to expand...
Click to expand...

& Fund &
".xls]Export'!" & "$A$9:$A$93",0)

Is that possible, and how can I do this?

thanks for any adviece or assistance

Philip
Click to expand...

.
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
not possible without VBA. See the following link for a compilation of
alternatives. In this case probably INDIRECT.EXT (from the morefunc.xll
add-in) sould work:
http://tinyurl.com/2c62u

--
Regards
Frank Kabel
Frankfurt, Germany

Govind said:
Hi Frank,

Thanks for the tip.

Any other formula suggested for this issue?

Regards

Govind.

Frank said:
Hi Govibd

this will only work if the other file is open as INDIRECT
won't work on closed files :)

-----Original Message-----
Hi,

You can try

=MATCH(A5,INDIRECT("'" & DirectoryPath & DateFolder
Click to expand...

& "[Export" & Fund
&".xls]Export'!" & "$A$9:$A$93"),0)

Regards

Govind.



Philip wrote:

Hi,

I have this to find the row of data in another workbook:

=MATCH(A5,'T:\Excel\ABM Amro Expenses\Expenses\NAV
ALERT\EXPORT
ARCHIVE\SICAV\20040816\[ExportXXXX.xls]Export'!
Click to expand...

$A$9:$A$93,0)

where the XXXX is a fund like IK05 or MMAF...

What I need is something like this:

=MATCH(A5,"'" & DirectoryPath & DateFolder & "[Export"
Click to expand...

& Fund &
".xls]Export'!" & "$A$9:$A$93",0)

Is that possible, and how can I do this?

thanks for any adviece or assistance

Philip

.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi,

using INDIRECT.EXT, I have this formula from the first answer:

=MATCH(A5,INDIRECT.EXT("'" & B2 & C2 &"\" & "[Export" & D2 &".xls]Export'!"
& "$A$9:$A$93"),0)

* B2 (DirectoryPath) = T:\Excel\ABM Amro Expenses\Expenses\NAV ALERT\EXPORT
ARCHIVE\MMF\


But I get a #VALUE or #REF ...

any ideas?

thanks

Philip

* C2 (Date) = 20040816

* D2 (Fund) =
Frank Kabel said:
Hi
not possible without VBA. See the following link for a compilation of
alternatives. In this case probably INDIRECT.EXT (from the morefunc.xll
add-in) sould work:
http://tinyurl.com/2c62u

--
Regards
Frank Kabel
Frankfurt, Germany

Govind said:
Hi Frank,

Thanks for the tip.

Any other formula suggested for this issue?

Regards

Govind.

Frank said:
Hi Govibd

this will only work if the other file is open as INDIRECT
won't work on closed files :)


-----Original Message-----
Hi,

You can try

=MATCH(A5,INDIRECT("'" & DirectoryPath & DateFolder

& "[Export" & Fund

&".xls]Export'!" & "$A$9:$A$93"),0)

Regards

Govind.



Philip wrote:

Hi,

I have this to find the row of data in another workbook:

=MATCH(A5,'T:\Excel\ABM Amro Expenses\Expenses\NAV

ALERT\EXPORT

ARCHIVE\SICAV\20040816\[ExportXXXX.xls]Export'!

$A$9:$A$93,0)

where the XXXX is a fund like IK05 or MMAF...

What I need is something like this:

=MATCH(A5,"'" & DirectoryPath & DateFolder & "[Export"

& Fund &

".xls]Export'!" & "$A$9:$A$93",0)

Is that possible, and how can I do this?

thanks for any adviece or assistance

Philip

.
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
INDIRECT.EXT won't work as second parameter in the MATCH function 8I
think Harlan explained this in your previous thread). Reason:
INDIRECT.EXT will only return the upper left cell value from a closed
workbook. So you have to use one of the other alternatives Halran and I
presented in your original thread

--
Regards
Frank Kabel
Frankfurt, Germany

Philip said:
Hi,

using INDIRECT.EXT, I have this formula from the first answer:

=MATCH(A5,INDIRECT.EXT("'" & B2 & C2 &"\" & "[Export" & D2 &".xls]Export'!"
& "$A$9:$A$93"),0)

* B2 (DirectoryPath) = T:\Excel\ABM Amro Expenses\Expenses\NAV ALERT\EXPORT
ARCHIVE\MMF\


But I get a #VALUE or #REF ...

any ideas?

thanks

Philip

* C2 (Date) = 20040816

* D2 (Fund) =
Frank Kabel said:
Hi
not possible without VBA. See the following link for a compilation of
alternatives. In this case probably INDIRECT.EXT (from the morefunc.xll
add-in) sould work:
http://tinyurl.com/2c62u

--
Regards
Frank Kabel
Frankfurt, Germany

Govind said:
Hi Frank,

Thanks for the tip.

Any other formula suggested for this issue?

Regards

Govind.

Frank Kabel wrote:
Hi Govibd

this will only work if the other file is open as INDIRECT
won't work on closed files :)


-----Original Message-----
Hi,

You can try

=MATCH(A5,INDIRECT("'" & DirectoryPath & DateFolder

& "[Export" & Fund

&".xls]Export'!" & "$A$9:$A$93"),0)

Regards

Govind.



Philip wrote:

Hi,

I have this to find the row of data in another workbook:

=MATCH(A5,'T:\Excel\ABM Amro Expenses\Expenses\NAV

ALERT\EXPORT

ARCHIVE\SICAV\20040816\[ExportXXXX.xls]Export'!

$A$9:$A$93,0)

where the XXXX is a fund like IK05 or MMAF...

What I need is something like this:

=MATCH(A5,"'" & DirectoryPath & DateFolder & "[Export"

& Fund &

".xls]Export'!" & "$A$9:$A$93",0)

Is that possible, and how can I do this?

thanks for any adviece or assistance

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

Help using INDIRECT / INDIRECT.EXT with paths created by FUNCTIONS 4

Top