Method-3: Using IF Statement and Named Range in Data Validation Formula in Excel. You will get the following result. dropdown.parentNode.submit(); In the Ribbon, select Data > Data Tools > Data Validation. Excel Charts Highlight the range required eg: B3:B8. Previously, we created a drop down list where our range of values was in the same sheet. The Information style shows a message that automatically authorizes the item no matter what the user gave. Or you can use the fill handle to copy the data validation in multiple cells. However only the contents of the first of these cells will appear, and no drop-down menu presenting all . ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. Hi, I was searching google and figured this is from list-data validation. Next, I have created a data validation list in the sheet called 'Input' whereby on can input any deadlines from the list found in the 'information' tab. The COUNTIF function simply counts occurrences of the value in the list. Syntax. You can set-up the date validation in Excel. This page explains how to create a your own validation rule for lists when you don't want the dropdown behavior. Now, click any data that you want to enter in the cell. Pivot also wouldn't work because it would require refreshing. One of them is using the Comma Separated Values that we showed earlier. Now if you are also needing to automate the named range bits, you could change them to encompass entire columns and then use a pivot table to pull in the data. Use the Snipping tool to post a screen shot of your data. expression.Formula1.