Forum Discussion
Solved
Data Validation List with multiple choices based on one cell (w/o VBA)
Good Afternoon, Please see the attached excel workbook for reference. I am trying to create a data validation list (Sheet: Flight Calculator - B13) to select all airports in a State (Sheet: C...
marshalltj67 Give this formula a shot, entered directly in Data Validation as the Source:
=OFFSET('CONUS Travel Source'!$B$2, MATCH($B$7, 'CONUS Travel Source'!$B$3:$B$385, 0), 6, COUNTIF('CONUS Travel Source'!$B$3:$B$385, $B$7))I tested it on my old laptop in Office 2010 just to be sure, and it seems to work just fine. Try it out and see if it's what you wanted.
What version of Excel do you have? Is it MS365 by chance?
I am using a work computer (managed by admin) with Microsoft Office Professional Plus 2016 and don't have access to IFS statements.
marshalltj67 Give this formula a shot, entered directly in Data Validation as the Source:
=OFFSET('CONUS Travel Source'!$B$2, MATCH($B$7, 'CONUS Travel Source'!$B$3:$B$385, 0), 6, COUNTIF('CONUS Travel Source'!$B$3:$B$385, $B$7))I tested it on my old laptop in Office 2010 just to be sure, and it seems to work just fine. Try it out and see if it's what you wanted.
- Worked perfectly!
Thank you so much!marshalltj67 You bet! I don't know why I referenced cell B2 and offset 6 columns, lol. You can also just reference cell H2 (the column with the airport list) and offset 0 columns to get the same results:
=OFFSET('CONUS Travel Source'!$H$2, MATCH($B$7, 'CONUS Travel Source'!$B$3:$B$385, 0), 0, COUNTIF('CONUS Travel Source'!$B$3:$B$385, $B$7))Cheers!