scroll to or hyperlink to a cell from a value entered

  • Thread starter Thread starter Charno
  • Start date Start date
C

Charno

I have a list of data in D15:D3014 (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 D15:D3014 then the
page will scroll to the matching data or create a hyperlink to the matching
data..........
 
=HYPERLINK("#Sheet1!D" &(MATCH(J9,D15:D3014)+14),"go there")

adjust the sheet name to match your sheet name.
 
Gary .....your a star, Cheers mate


Charno

Gary''s Student said:
=HYPERLINK("#Sheet1!D" &(MATCH(J9,D15:D3014)+14),"go there")

adjust the sheet name to match your sheet name.
Click to expand...
 
With J9 reserved for you to enter a value, put this in (say) K9:

=IF(J9="","",HYPERLINK("#D"&MATCH(J9,D153:D3014,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
 
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
 
Back
Top