create hyperlinks in one step - find text - return adress - create hyperlink

Hello all,

I would like to reduce to one step the way in which I create hyperlinks and allow the formula to be scroll down.

Currently I link the company list in Sheet1 to the specific company information in sheet2 in two steps.

a) I find the cell address of the company name in sheet2 by:
=ADDRESS(MATCH(A2,Sheet2!A:A,0),COLUMN(Sheet2!A:A),4)

b) I create the fix hyperlink by introducing the cell addressed returned on the previous step on the following formula:
=HYPERLINK("#"&CELL("address",Sheet2!A1),"further info CLICK")

I would like to join the two formulas in such way that when I do the formula in sheet1 for the first company of the list I can scroll it down for the rest of the company index.

Any idea on how to possible achieve it?

Regards

You can do it like this:

=HYPERLINK("#Sheet2!R"&MATCH(A2,Sheet2!A:A,0)&"C1","further info CLICK")

Hope this helps.

Pete

Both formulas work beautifully!!!

Thanks guys for making the world a more productive place