I need to populate a dropdown on sheet1 with data from sheet2 columnA. I can do this no problem. However, I only want the items from columnA to appear in the sheet1 dropdown if sheet2 columnB has an 'x'.
Asked
Active
Viewed 347 times
0
-
You can't do this directly. First you need to filter data to a column based on criteria `x`. then in validation rule you have to refer those filtered range. What is your excel version? – Harun24hr Aug 16 '21 at 02:51
2 Answers
1
Screenshots/here refer (updates marked *):
Demonstration
Only values 1-10 have corresponding 'x' in 'col B' (here, d:d), whereas 101-112 correspond to 'y'. Output as follows:
Assuming you have Office 365 compatible version of Excel,
Lookup for validation list (F3#)
=SORT(UNIQUE(FILTER(C3:C41,--ISNUMBER(SEARCH("x",D3:D41)))))
(easily customisable for different range cols A, B - sheet_2)
Validation list
Reference lookup range directly (*updated thanks to note by @Harun24HR)
=F3#
Extras
If interested, a unique list of omitted values (cols A & B, sheet_2) can be determined in a similar yet dependent fashion (cf. first equation):
=SORT(UNIQUE(FILTER(C3:C41,--NOT(ISNUMBER(MATCH(C3:C41,F3#,0))))))
JB-007
- 2,156
- 1
- 6
- 22
-
No need named range. You can directly use `=Sheet_2!$F$3#` to validation list. – Harun24hr Aug 16 '21 at 03:38
-
I thought I was going to end up having to create another column. Thanks for the formula! – pnkflydgr Aug 16 '21 at 19:35
-
Yep, silly limitation re: validation lists I'm afraid - adding to list I'll be picking up with Bill next time we play golf ;) – JB-007 Aug 17 '21 at 19:03
0
You can just use INDIRECT in the data validation statement that refers to a cell with an IF statement.
For example, in the below picture, my dropdown is based on the formula =INDIRECT($G$9) and the value of G9 is determined by =IF(ISNUMBER(MATCH("x", H3:H7,0)), "G3:G7", "G8")
More generally, just use =IF(ISNUMBER(MATCH("x", test_range)), drop_down_range, empty_cell) and be sure to use quotes around the dropdown range.
Test 1:
Test 2:
EDS
- 2,155
- 1
- 6
- 21