Qualified Validation Picklist of Dynamic Size
- The validation picklist(s) Dynamically Grows & Shrinks.
- Exclude a picklist selected value from subsequent picklists.
- Two picklists mutual exclusion of other picklist value.
- Two formulas:
- Helper column validation picklist
- Data Validation List Source
The "Receiving" (opened) picklist does not have option d available
because it has already been selected by the "Sending" Picklist.
Helper column; dynamic validation picklist formula:
=INDEX($I$34:$I$38,AGGREGATE(15,6, ROW($I$34:$I$38)/($I$34:$I$38 <> $K$42),ROW($A1)) - ROW($I$34)+1)
- Dynamically Generated Validation Picklist (Helper Column).
- Paste to J34 in image; First cell of "Sending Validation" range.
- Not a CSE, just drag copy down to length of Original Picklist.
$I$34:$I$38 Common (Original) Validation List Range.
$K$42 Excluded Picklist Cell (other picklist cell has value not available here).
- Repeat for Second, (Third, Forth...) Validation Picklists
- The picklist in
$K$42 uses this validation list.
- For this validation picklist formula, change
$K$42 to the picklist using the first helper column created:
$J$42 in the image.
- Mod for additional cell values to exclude.
($I$34:$I$38 <> $K$42) →
(($I$34:$I$38 <> $K$42)*($I$34:$I$38 <> $Z$42))
Data Validation List Source formula:
- Dynamically Sized Range
- paste to the Validation Source text box for the picklist generated by the first formula.
$J$42 First picklist in image (excluded from second validation picklist)
$K$42 Second picklist in image (excluded from first validation picklist)
$J$34 First Cell where the Helper column validation picklist formula was pasted.
$J$34:$J$38 Range of all cells containing the Helper column picklists formula.
More notes about these formulas:
- Both the helper column formulas and validation source address references will modify properly when the cells they reference are cut and pasted.
- All but one reference in the helper column formula are absolute so they drag/copy properly.
- When Starting a new formula, be sure the relative row address of
ROW($A1) is one.
- The Data Validation List Source formula does not need absolute addresses.