Is the solution benefit worth the cost?
With any product there is a balance between the number of features versus the cost/performance. Race cars and stretch limos have different features and different purposes.
Spreadsheets are nothing like cars but as you add more features the file size and calculation time will increase.
Model design includes deciding what features to include and which features to omit. Sometimes we boil this down to “nice to have” versus “must have” features.
Super Dynamic Data Validation List
Let’s look at an example. We have a data validation list that we don’t want anyone to modify. This list and sheet must be hidden from all users of this Excel file. However, we also want people to be able to type in values as well as selecting from our secure list. These typed in values should then be automatically added to our data validation drop down list. The next time we click on the data validation list we should now see the value that we’ve manually typed.
Is it possible?
Technically yes it is. We could use VBA, Excel’s programming language, or a series of formulas to solve this.
Is it worth the trouble?
That’s something that you would have to decide. My solution will be formula based. Yes, we could create VBA event code to monitor typed in values and then copy/paste it into the secure list but my VBA skills are very rusty.
Download my Excel file
Download here or via my OneDrive (file 00120)
Watch my YouTube video
Read the steps below
Step 1
Uncheck “Show error alert after invalid data is entered”. This allows you to either select a value or type in a value.
Step 2
Use an array formula to create a list of the typed in values (those values that people type in and are not selected from data validation drop down).
=SMALL(IF(NOT(ISBLANK(Fruit_Selection[Select_Fruit]))*
ISERROR(MATCH(Fruit_Selection[Select_Fruit],Fruit_Hidden_List[Fruit_List],0))),
ROW(Fruit_Selection[Select_Fruit]),””),$D11)
I have color coded the formula to make it a bit easier to understand.
NOT(ISBLANK(Fruit_Selection[Select_Fruit])) Array Condition 1: Make sure that we ignore blank cells in the selection area.
ISERROR(MATCH(Fruit_Selection[Select_Fruit],Fruit_Hidden_List[Fruit_List],0)) Array Condition 2: Typed in values that won’t be found in the secure list.
ROW(Fruit_Selection[Select_Fruit]),””) When both conditions above are met, then I want to get the row numbers to vertically identify typed in values.
Finally, I wrap the array with the SMALL function and we extract the row numbers that are stored inside of our array formula.
Step 3
=INDEX(Fruit_Selection[[#All],[Select_Fruit]],$E11) Here, I simply use the INDEX function to get the text value using the array formula row number from step 2. I could have wrapped this around the array result in the same formula.
Step 4
=IF(D11>$G$4,OFFSET($F$10,D11-$G$4,0),OFFSET(Fruit_Hidden_List[[#Headers],[Fruit_List]],D11,0))
D11>$G$4 The logical test portion of my IF function compares the counter value with cell $G$4 which counts the number of text values in our secure list.
OFFSET($F$10,D11-$G$4,0) When the IF statement above shows false then it means that we need to extract another item from the secure list.
OFFSET(Fruit_Hidden_List[[#Headers],[Fruit_List]],D11,0) When the IF statement is true, we get the typed in values.
Step 5
=OFFSET(SOLUTION!$G$11,0,0,SOLUTION!$G$6,1) Now, we create a dynamic named range using this final formula. This named range includes text values from our secure list and also includes all of the typed in values.
Step 6
Finally, we go back to our data input area and in the data validation area we select the named range that we created in Step 5.
The solution required 6 steps which included a couple of complex formulas. Is the benefit of the solution worth the cost of these steps? Imagine a much larger list or a model that needs many of these complex solutions. Some requirements are absolutely necessary and others are only worth including if there’s an easy way to solve it.