Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

jump from a drop-down list to a cell

Copper Contributor

I know this has been asked before, but I'll be darned if I can find a suitable answer.

I have a drop-down list of names in cell Z3 (created with data validation).

Currently I have a conditional format to color the cell with the name selected from the drop-down to simplify the search.

My wish is to select a name from this dropdown list, then automatically jump to the text-matched cell.

I want to keep it simple without using macros - I'm positive there's a way - maybe using hyperlink, but I haven't figured it out.

Here's a screenshot:

eric1969_0-1701289281727.png

 

 

2 Replies
I understand that you want to jump to a cell that matches the name you select from a drop-down list in Excel. This is a common question that many Excel users have, and there are different ways to achieve this.

One possible solution is to use the HYPERLINK function in an adjacent cell to your drop-down list. This function creates a clickable link that takes you to a specific location in your workbook. You can use the drop-down value as the link text and the cell address as the link location. For example, if your drop-down list is in cell Z3, you can enter this formula in cell AA3:

=HYPERLINK("#"&Z3,Z3)

This will create a link that displays the name you select from the drop-down list and takes you to the cell with the same name. You can copy this formula down to other cells if you have multiple drop-down lists.

Another possible solution is to use a worksheet_change macro that triggers when you change the value of the drop-down list. This macro can use the Find method to locate the matching cell and activate it. However, this solution requires some VBA coding and enabling macros in your workbook.

You can also watch some video tutorials on how to create drop-down lists in Excel and link them to other cells. Here are some links to the videos I found:

I hope this helps you solve your problem.
WOW! That was a fast response!
I tried that formula in AA3 as suggested, but it returned an error 'Reference isn't valid'.
I should've stated that the names in C column are referenced from another worksheet. (Not sure if that makes a difference)