Forum Discussion
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: CONUS Travel Source) based on a cell outputting the assigned state (Sheet: Flight Calculator - B7). ALL the airports for each State are in the CONUS Travel Source sheet and I need the data validation list to output all airports in accordance with the assigned State.
I tried using IF statements but will run out character space within the data validation tool. I also tried using INDEX and MATCH function but could only get it output the first listed airport instead of list of all the airport. I also tried assigning each state a number/assigning each airport a number but no luck.
Thanks in advance!
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.
8 Replies
- This is the current formula I have in the data validation list:
=INDEX('CONUS Travel Source'!$H$3:$H$385,MATCH($B$7,'CONUS Travel Source'!$B$3:$B$385,0))
Do I need to apply a ROWS() somewhere so the formula can MATCH to a certain number of rows in accordance with the assigned State?
Thanks!- 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.
