Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

You can make the formula in Validity > Cellrange independent of category and item count. To get this, you need to define names for the categories cellrange and the items cellrange and hold the categories and items values in a separate part of the sheet.

For example (see attached file)
Name myCategory for F1:G1, holding Categories "Color" and "Size" and name myItems for F2:G4 holding column "red","blue","green" in F and column "small","medium","large" in G.

The formula for validity in A2 is myCategory.
The formula for validity in B2 is INDEX(myItems;0;MATCH(A2;myCategory;0))
When you add or remove categories or items, you only need to adapt the information in the definition of the names.