Forum Discussion

Brass Contributor

Nov 25, 2023

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...

excel

Formulas and Functions

djclements
Nov 25, 2023
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.

marshalltj67

Brass Contributor

Nov 25, 2023

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!

djclements

Silver Contributor

Nov 25, 2023

What version of Excel do you have? Is it MS365 by chance?

marshalltj67
Brass Contributor
Nov 25, 2023
I am using a work computer (managed by admin) with Microsoft Office Professional Plus 2016 and don't have access to IFS statements.
- djclements
  Silver Contributor
  Nov 25, 2023
  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.
  - marshalltj67
    Brass Contributor
    Nov 25, 2023
    Worked perfectly!
    Thank you so much!