Indirect and Match

[shyguy44]

Currently have

=IF(ISNUMBER(MATCH(A22, 'C:\MyDocs\[Test.xls]Sheet2'!$A$1:$A$5000,0)),'C:\MyDocs\[Test.xls]Sheet2'!A$1,"")

What I want to do is instead of using pathname above, create a path name in a cell then use indirect to refer to the path name but I'm struggling with it

Thanks

 

[oldbrewer]

									B4
red	333	7	8	blue
						yes
						=IF(ISNUMBER(INDIRECT(J1)),"yes","no")
						so J1 can contain any path you want

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[shyguy44]

Not sure how it will check for a22, which I want to copy the formula down. Also not sure now if indirect will work for closed workbooks

 

[oldbrewer]

=if(iserror(match........),blank, your formula) ............reference the closed workbook directly

you can build the path as a series of text segments - I forget how - start a new thread to get someone to solve that bit