Linking to a matched cell across sheets

[EK01]

I am trying to find a combination of formulas that will look at a cell in Sheet1 and find the match across several sheets. I have the formula(s) that links to the match in a specific sheet, but I would like to be able to look for the match in all sheets and then link to it.

Thanks,
EK

[JBeaucaire]

This will work for the two sheets shown:

=IF($C2=0,"",HYPERLINK("#"&CELL("address",IF(ISNUMBER(MATCH(A2,Cash!B:B,0)), INDEX(Cash!B:B,MATCH(A2,Cash!B:B,0)), INDEX(AR!B:B,MATCH(A2,AR!B:B,0)))),A2))

You could probably go a sheet or two above that in the formula without a lot of hassle, but beyond that you may need to consider using VBA for that.

[EK01]

Ok, thanks. I will have about 20 sheets, so I'll start looking into the VBA possibilities. I was hoping for a simple "AllSheets!" name instead of listing each tab, but that would have been too easy

[JBeaucaire]

I have a 3D VLOOKUP formula technique posted here:

3D VLOOKUP

Let me ponder that a bit and see if I can fathom a way to convert that into a 3D HYPERLINK().

[EK01]

Stunning!! Thanks!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.