Thread: VBA match index, display multiple values with selectable reference file path

Right lads,

{=INDEX('F:\General\Shared\Hendersona\For Mohseen Mohemmed\RTU whitebook\[ANDREW OVERALLCorsch.dbf]ANDREW OVERALLCorsch'!$A$2:$B$5000, SMALL(IF($L$30='F:\General\Shared\Hendersona\For Mohseen Mohemmed\RTU whitebook\[ANDREW OVERALLCorsch.dbf]ANDREW OVERALLCorsch'!$A$2:$A$5000, ROW('F:\General\Shared\Hendersona\For Mohseen Mohemmed\RTU whitebook\[ANDREW OVERALLCorsch.dbf]ANDREW OVERALLCorsch'!$A$2:$A$5000)-MIN(ROW('F:\General\Shared\Hendersona\For Mohseen Mohemmed\RTU whitebook\[ANDREW OVERALLCorsch.dbf]ANDREW OVERALLCorsch'!$A$2:$A$5000))+1, ""), ROW(B1)),COLUMN(B1))}

NB: array formula. (Cntrl, Shift, Enter) if this fits anyones current needs

That is my formula i have, it scans another file (call it coresheet 1) for a cable number found in cell L30 then returns all the core names associated with that cable (obviously the next cell will read identical but with "Row(B2)),COLUMN(B2))" at the end.

this formula works for my purpose however,
I wish to make this Work with interchangeable files, e.g Andrew OverallCorsch.dbf changing to Peter OverallCorsch.dbf (always a dbf) however the filename and filepath may change,

i wish to do this same function using vba, displaying the core names however with a browse window to select this sheet.

then it to display all the core names in a seperate cells one bellow the other.



I have tried,

In VBA constructing an exact replica of

 'F:\General\Shared\Hendersona\For Mohseen Mohemmed\RTU whitebook\[ANDREW OVERALLCorsch.dbf]ANDREW OVERALLCorsch'!$A$2:$A$5000

to another location but it does't work, excel just selects the

 'F:\

and tells me an error when i input it, however when i manually select the data it works fine.


I am loosing my sanity over this one.

Thanks.

All help much appreciated