Looking to create a drop-down list where selecting a name brings me to a specific cell containing that name

[kjt2904]

I'm doing a fantasy football statistical sheet where I have a number of sheets by position (running back, wide receiver, tight end) and I don't want to list everybody out and link to that on the frozen header up top. I'd like to be able to use a drop-down list to select the player I'm looking for and then jump to that part of the sheet. How can I go about this in the most easy and streamlined way?

Example of how I'd like the process to go:

1. User wants to look up statistics for Cooper Kupp (wide receiver)
2. User clicks on wide receiver tab
3. User clicks on drop-down, scrolls for "Cooper Kupp"
4. User selects "Cooper Kupp"
5. User is then brought down the sheet to the matching Cooper Kupp section within the same sheet containing his statistics
6. Repeat steps 1-5 for any other player they would like to find statistics for

Note that I'm not well-versed in Excel, especially with anything complicated so there might need to be some handholding in terms of complicated steps if that is what it takes.

Thank you in advance!

 

[SanjayGMusafir]

Welcome to the forum

Your problem has a 3 step solution

1. Create Named ranges
2. Create a Drop Down List using the Names of Named Ranges
3. In some adjacent cell of your choice, create a hyperlink to the selection from that Named range

Check this and revert -

Book1
	A	B	C	D	E	F
1
2
3		Trial1	Go to Trial1
4
5
6
7
Sheet1

Cell Formulas
Range	Formula
C3	C3	=HYPERLINK("#"&B3,"Go to "&B3)

Cells with Data Validation
Cell	Allow	Criteria
B3	List	Trial1,Trial2

 

[kjt2904]

This is how I have my sheet set up thus far, I'm trying to plug in those formulas but still having trouble... like I said, I'm not very well versed in the inner workings of Excel... how would this translate to the sheet I have as constructed? I'd like the dropdown to be where I have it labeled, once I select say A.T. Perry, the cursor would automatically go to A32, then further down (A43, A54 for players further down, etc.)

I apologize for being such a novice with stuff like this, and thank you so much for the assistance!

 

[SanjayGMusafir]

1. Create Named ranges - Name the ranges where you want dropdown links to reach
2. Create a Drop Down List using the Names of Named Ranges - Use the created names in Dropdown list
3. In some adjacent cell of your choice, create a hyperlink to the selection from that Named range - You can create hyperlink in Cell I4 with following formula

=HYPERLINK("#"&A4,"Go")

By clicking on Cell I4 people will navigate through the worksheet just as you want.

 

[kjt2904]

1. Create Named ranges - Name the ranges where you want dropdown links to reach
2. Create a Drop Down List using the Names of Named Ranges - Use the created names in Dropdown list
3. In some adjacent cell of your choice, create a hyperlink to the selection from that Named range - You can create hyperlink in Cell I4 with following formula

=HYPERLINK("#"&A4,"Go")

By clicking on Cell I4 people will navigate through the worksheet just as you want.

so I went to Data then Data Validation with A4 selected
then under "Allow", selected "list"
and then source is the cells I want in the dropdown? I feel like I'm missing something here, I'm just wondering knowing the cell information how you would do it with the information I provided, because I do feel like I'm missing something

again, appreciate all of your help!

 

[SanjayGMusafir]

and then source is the cells I want in the dropdown? I feel like I'm missing something here, I'm just wondering knowing the cell information how you would do it with the information I provided, because I do feel like I'm missing something

Here you can work 2 ways

1. Best is to create a list at a seperate place.
   1. Make that list also a Named range
   2. Then refer that list for Data validation
   3. This gives you flexibility of adding or removing any part at any time.
2. Easier (but less recommended) practice is to type names manually using comma

Check the article below for data Validation.

Excel Drop Down Lists - Data Validation

Easy steps to make drop down list wth Excel data validation. Show list of valid entries, reduce data entry errors. Videos, written notes, free workbook

www.contextures.com

And this article for Named Ranges

Excel names and named ranges: how to define and use in formulas

The tutorial explains what Excel name is and how to define a name for a cell, range, constant or formula. You will also learn how to edit, filter and delete defined names in Excel.

www.ablebits.com