How do we generate those fancy validation lists in excel you ask?
I am happy to announce that I am again actively writing on my page! This time the cause is that I am not happy that I spent a lot of time finding out why validation lists are so cruelsome to some. Ok let us get to the point, shall we?
You want to validate inputs in a column based on a list you have defined. You should use the following approach:
First create the following structure in EXCEL and press “CTRL+F3”:
Next Name your range:
Next close the dialogue:
Choose the Cells you want to keep validated:
Select the following parameters and input your range-name. In this stage, if you created your validation range by using “Format as Table” there is a possibility that it would not work and fail with a message that your formula is wrong!:
Important! In order to link tables to ranges just create the table first with “Format as Table” and link the specific range inside the table. It should be referenced now with a structural reference e. g. “Table1[mycolumnname]”. You can distinguish them also in the Name: