Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Drop down box option goes to cell location

0

Hi there,

I need a drop down box which will contain all the values in a certain column, ignoring all the empty cells. When you click on the option in the dropdown box it goes to where the data is. This will help me navigate my data as there are hundreds of rows.

Is this possible.

Thanks

excel
MS Office Version: 2019
posted by: Dave1245 (rep: 8) on Mon Jul 6, 2020 at 3:16 pm
Answer
Discuss

Discussion

Your question is incomplete. I think you ahve a number of sheets, each sheet with a different event. You want a drop-down from which to select an event that this selection should act like a hyperlink to open the sheet with that event. The events change but the sheet with the dropdown remains. All of this is guesswork because you don't describe anything but if it's roughly correct the two key factors are the sheet that always remains and the names of the sheets that change. Even if you have no control over the sheet names and must identify the sheets by the value in their column K it will not be possible to realise your ambition with worksheet functions. You will need vba to be deployed to prepare the list and eitehr create the hyperlinks in it or take over the functionality of such links.
Variatus (rep: 4889) Jul 7, '20 at 11:37 pm
All of the relevant data shown in the attachment is on one sheet and appears in that format. I made the title of the event ie Women's 100m paste into column K so that the data validation range would be able to see the title. I don't know how each item in the dropdown list 'the values from column K' can be hyperliked automatically so when you click on an item in the dropdown list the view will go straight to the title selescted i.e Women's 100m in the dropdown list is selected you will then be taken to the whole result. This will make it esier to find the desired result when at the end of the day there are over 100 results and scrolling to the bottom or finding the desired one will be difficult, especially when looking at it on a mobile phone.

Thanks
Dave1245 (rep: 8) Jul 8, '20 at 1:55 pm
Add to Discussion

Answers

0

Yes, it's possible. Create a named range for the list and link your data validation drop-down to that range. Then fill the range with a formula that extracts the names you want leaving the blanks - and probably duplicates, too - behind. Since the length of the list is probably not predictable the range must be defined to be dynamic.

All of this is perfectly described, step by step, at this site. Let me know if you need help with the implementation.

posted by: Variatus (rep: 4889) on Mon Jul 6, 2020 at 10:37 pm
Discuss

Discussion

Thanks,
Not quite sure how to implement it, the value in column K is what I would like to appear in the drop down list and when selected from the drop down list it will go to where the data is. 
I've attached a sample copy in the original postwhich contains a small amount of data, the value in column will be different every time when used for real.
Thanks for your help
Dave1245 (rep: 8) Jul 7, '20 at 11:59 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login