The Data Validation tool in Excel can be used to easily create lists.

However if you have many entries, you may need to break the list into multiple dependent drop down lists.

By creating a cascading set of lists, the entry in one is dependent upon the selection made in another.

This is vital as it will establish the relationship between the lists.

For this to work, the name given to a range will need to match the wording of the item from the previous list.

Each range will be named the same as the header for each list.

For example, range C2:C5 is named Films, which precisely matches the wording of Films in the Category list.

The first list is created normally, the other dependent lists will use the INDIRECT function to reference the previous list entry.

This function converts the text (selected item) to a reference (named range).

In this example, the reference must be entered without so that it is not fixed.

I know that this is because it was referencing a blank but how can I leave the second cell blank until the first selection is made.

For example IFERROR(the formula producing REF,) The will show a blank until the first list is used.

Excel Data Validation Multiple Selection Code For Mac