scroll to or hyperlink to a cell from a value entered

[Charno]

I have a list of data in D153014 (no duplicates).
What i want to do if possible is set up a lookup cell (say in J9) so that
when data is entered into cell J9 that matches data in D153014 then the
page will scroll to the matching data or create a hyperlink to the matching
data..........

 

[Gary''s Student]

=HYPERLINK("#Sheet1!D" &(MATCH(J9,D153014)+14),"go there")

adjust the sheet name to match your sheet name.

 

[Gary''s Student]

Sorry:

=HYPERLINK("#Sheet1!D" &(MATCH(J9,D153014,0)+14),"go there")
instead

 

[Charno]

Gary .....your a star, Cheers mate


Charno

=HYPERLINK("#Sheet1!D" &(MATCH(J9,D153014)+14),"go there")

adjust the sheet name to match your sheet name.

 

[Pete_UK]

With J9 reserved for you to enter a value, put this in (say) K9:

=IF(J9="","",HYPERLINK("#D"&MATCH(J9,D1533014,0)+14,",jump"))

The word "jump" will appear in K9 when J9 is not empty - just click on
that cell to jump to the appropriate cell in column D.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, should be D15 not D153.

Hope this helps.

Pete

 

[uturnabout]

What can be done if the data is across columns instead of down rows?

 

[Pete_UK]

You would change the range in the second parameter of the MATCH
function to suit your data, and you will probably need to change the
14 to a number appropriate to your layout. It was 14 in this case
because the data started in D15. If your data started in H5, for
example, the number would be 7 as H is the 8th column.

Hope this helps.

Pete