Hi Forum members,
I have found many, many different approaches on this subject but I have been unable to tweak them to fit my scenario. Below is my scenario which references the attached spreadsheet.
The data in Column A of the Congregations tab forms the named range Cong_Name. This named range becomes the source for data validation lists in Column F of the Bookings tab. However, the data in the Cong_Name range is 200+ lines and growing so a standard data validation is not user friendly. Thus, I am searching for a solution that dynamically reduces the list values based on the characters entered into a cell of Column F of the Bookings tab. Additionally, at least 200 cells in Column F of the Bookings tab require the data validation list, not just one field.
The best solution that I have found that has closely resembled my needs is from Neil Firth in two parts on Youtube. (See attached screenshots for URL links to these videos)
- Create a searchable drop down list in Excel
- Create a searchable drop down list in Excel Part 2
I have come unstuck with the =CELL("contents") formula in the last part of the 2nd video and cannot replicate the same end result. Not sure if it an error with how I have replicated the formulas or based the formulas span 2 tabs instead of the 1 tab as in his video. I have reached out to Neil but yet to hear back from him so I was hoping that there might be some Excel experts out there that can point me in the right direction.
Many thanks,
Tim
Bookmarks