How to find sheet in formula by (part) cell reference. use Indirect ???

[KrisVeen]

Dear all,

Your help would be much appreciated.

How can I refer in a formula to a sheet by using the first three letters of a cell. I have two files and I need to find the sheet in one file by the reference of the product code (THA005). I understand this is possible with INDIRECT and INDIRECT.EXT which requires the morefun.xll add-in.

The value of cell A13 is THA005. The sheet name in the other file is called THA. The formulas I have to integrate this in are underneath. Any ideas how to integrate this into the formulas?

(original formula)
=MATCH(A13, [TFWebsite.xls]Tha!$A$5:$A$5000,0)
(original formula)
=IF(ISNUMBER(C13),INDEX([TFWebsite.xls]Tha!$C$5:$C$5000,C13),0)

(my own attempt, doesn't work)
=MATCH(A14, [TFWebsite.xls] indirect(A14&!$A$5:$A$5000,0))

My own attempt doesn't work, probably because of the fact that the cell value is THA005 while to sheet is called Tha. In addition most likely a problem because the sheet is in another file. ?????

Your e-mail on this is welcome veenkris@yahoo.co.uk

Many thanks,

Kris

[MSP77079]

I think it will work if you put the entire address inside the Indirect function, and enclose it in quotes.

Instead of

=MATCH(A14, [TFWebsite.xls] indirect(A14&!$A$5:$A$5000,0))


Try

=MATCH(A14, indirect("[TFWebsite.xls]"&A14&"!$A$5:$A$5000"),0)

[VBA Noob]

Hi,

Try

=MATCH(A14,indirect("[TFWebsite.xls]"&left(A14,3)&"!$A$5:$A$5000"),0)

Should return the sheet name THA

VBA Noob

[KrisVeen]

Thank you the first formula (MATCH) works fine.
=MATCH(A14,indirect("[TFWebsite.xls]"&left(A14,3)&"!$A$5:$A$5000"),0)

How about the second formula, if I try the same concept there I mess up and get an error. This is what I tried:
=IF(ISNUMBER(C14),INDEX("[TFWebsite.xls]"&left(A14,3)&"!$C$5:$C$5000,C14),0)

Please look above for the original IF formula and advise me what should be correct. Same concept, to look for the sheet by the first three letters of the cell value.

I can't see why this doesn't work????????

Thanks guys

[VBA Noob]

Hi,

Does this work. I assume A14 is still THA005

=IF(ISNUMBER(C14),INDEX(INDIRECT("[TFWebsite.xls]"&LEFT(A14,3)&"!$C$5:$C$5000"),C14),0)

VBA Noob

[KrisVeen]

That works perfectly,

I oversaw that, getting late.

Thanks a lot.

=IF(ISNUMBER(C14),INDEX(INDIRECT("[TFWebsite.xls]"&LEFT(A14,3)&"!$C$5:$C$5000"),C14),0)


PERFECT

[VBA Noob]

Glad you got a solution

VBA Noob