Validating data in excel

16-Feb-2021 12:33

In the example below, we have a list of cities in cell F2.

The selection from this list affects what names appear in the next list in cell G2. For example, range A2: A4 is named city, range B2: B6 is named cardiff and so on. The list in cell F2 is created just like in the previous example.

You cannot begin named ranges with a number, or use spaces and some other symbols.

So, if items in your list use spaces, or start with numbers it presents an obstacle.

If the answer is 0 then the value is unique and allowed.

When entering dates, you may need to restrict a user to the entry of specific days of the week. For this example, we shall restrict entry of dates that are Monday to Friday only. The WEEKDAY function returns a number that represents the day of the week.

In the WEEKDAY function, the 2 specifies that a week starts on Monday as 1 through to the Sunday as 7.

In this example, we prevent the entry of a future date.

Maybe users are recording a transaction that has occurred.

For this we can use a formula with the UPPER and the EXACT functions. Select Custom from the Allow list and enter the following formula into the Formula box. Unfortunately users entering the wrong date is also commonplace.