Following on from last week’s post on a single adjustment formula this post will share a more robust solution for including or excluding adjustments.

The data table we will use is on the left side of the sheet – see image below. You can download the example file at a button at the bottom of the post.

The report we will use is show below. The idea is that the user can select a region in cell F2 and then adjust the region’s total by selecting states to include or exclude.

There are three input areas. The Region (cell F2), the States to remove (cells F4:F5) and the States to Add (cells F7:F8).

Selecting regions

A recent change to Data Validation drop-down lists mean you can refer to a list that contains duplicates, but the drop-down list will only display the unique entries. The image below has the Data Validation dialog settings for cell F2.

Notice this refers to the range $B$2:$B$9. This range contains duplicates of regions.

When you use the drop down in cell F2 it only shows the unique regions – see below.

Selecting States

It is important that we make sure the user can only select valid states based on the region chosen in cell F2.

This means that:

• in the Remove States cells (F4 and F5) only states that are in the region can be chosen.
• In the Add States cells (F7 and F8) only states that aren’t in the region can be selected.

To drive these two selections, we will use Data Validation drop downs and helper cells on the right using the FILTER function – see image below.

The FILTER function in cell J2 that lists the states in the selected region is.

=FILTER(A2:A9,B2:B9=F2,”None”)

The FILTER function in cell L2 that lists the states not in the selected region is.

=FILTER(A2:A9,B2:B9<>F2,”None”)

These formulas both create spill ranges of the relevant states based on the table on the left.

Handling the Data Validation blind spot

One issue with Excel’s Data validation system is that it is possible to have invalid entries in Data Validation cells. For example, if you select a region in cell F2 and then make state selections based on that region, then selecting another region in cell F2 may make the previous states selected invalid.

The other way an invalid entry can exist is if someone uses paste special values into a Data Validation cell. Excel won’t stop or warn you the entries are invalid.

We can add more helper cells to warn the user and stop the calculation.

The formula for cell E4 which can copied to E5 is.

=COUNTIF($J$2#,F4)>0

This returns TRUE if the state in cell F4 is in the list of states in the spill range starting in cell J2. It returns FALSE if the start in the cell isn’t in the list starting in J2.

The formula for cell E7 which can copied to E8 is.

=COUNTIF($L$2#,F7)>0

This returns TRUE if the state in cell F7 is in the list of states in the spill range starting in cell L2. It returns FALSE if the start in the cell isn’t in the list starting in L2.

We can use a conditional format to change the cells in column E orange if they have FALSE. This is a warning to the user of an invalid state.

We can also multiply the formula in column G by the cell in column E to zero invalid calculations.

In Excel FALSE is converted to zero when you multiply by it. TRUE is converted to 1, so multiplying by TRUE leaves calculation unchanged.

You can see an example in the image below.

The state entries in row 5 and row 8 are invalid and the values are zero.

The relevant drop-down lists can be seen on the left for the East region.