Index formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Have a nice day! - 14 Sep 2007 04:40 GMT

I am trying to build a index for lack of a better name. I would like
to build a list several of the itmes "cells" at the beginning of a
spreadsheet .
When these items "cells" are double clicked, it would take you to the
corresponding cell on the spreadsheet. The index cell would have the
exact same information as the cell it would be looking for.

I am continually adding rows to this spreadsheet so the row # would
change as added.

This may not be possible, but I have seen you guys do amazing things
with excel on here.

Thanks and keep up the great work!!!!!!!!

John

Reply to this Message

Max - 14 Sep 2007 06:25 GMT

One play which delivers this using a single click hyperlink ..

Illustrated in this sample:
http://www.savefile.com/files/1054222
Hyperlink item to table range below.xls

Assuming items listed in A2:A10,
reference table range in rows 20 to 200 with items list in B2:B200

In B2:
=IF(A2="","",IF(ISNA(MATCH(A2,B$20:B$200,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("B"&MATCH(A2,B$20:B$200,0)+19)),A2)))
Copy down to B10. Hide away col A. The above creates hyperlinks in col B
which jumps to the correct row in the range below (B2:B200) which matches
with the item shown in the hyperlink.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I am trying to build a index for lack of a better name. I would like
> to build a list several of the itmes "cells" at the beginning of a
[quoted text clipped - 12 lines]
>
> John

Reply to this Message

Rate this thread: