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.
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!
- Hello!
I am getting instances where my reference is off by multiple rows. Do you know how to fix this in the reference column?