In a previous article, we showed how pull-downs can be used to enter items that are not on the list.
However, if you try to tabulate the data using the VLOOKUP function or the INDEX/MATCH function, the data will not be tabulated properly unless it is an exact match.
In such cases, it is possible to change the color of the cells so that you can see at a glance which items were entered manually.
See also this regarding pull-downs.
Set conditional formatting
First, select one of the cells you are going to "Conditional Formatting" this time.
Next, click on "Conditional Formatting" on the "Home" tab.
Click "New Rule" in the menu that appears.
Click on "Use formulas to determine which cells to format" in the Rule Types.
Enter the following function in the space provided below.
=ISNA(MATCH(Cell number selected this time, Dropdown Referer Range, 0))
In this example, the following is used.
=ISNA(MATCH(D3, Master! $C$4:$C$10, 0))Once you have entered the function, click on "Format".
From the "Fill" tab of the menu that appears, select the color you want and click the "OK" button.
Click the "OK" button again.
This completes the formatting of the first selected "D3" cell.
Copy formatting
Then copy the function to the remaining cells.
With "D3" cell selected, press [Ctrl]+[C] to copy, then right-click with "D4" to "D8" cells selected.
From the "Paste Options" menu that appears, click on the "Format" button.
The formatting is now copied to the remaining cells.
Blank fields do not seem to be pulled by the MATCH function, so as you can see, they all turn yellow.
If this display is bothering you, you need to add a condition to make it "white" if it is "blank".
No color if blank formatting
Select one of the cells you are trying to "conditional format" again.
Next, click on "Conditional Formatting" on the "Home" tab.
Click "New Rule" in the menu that appears.
In the Rule Types, this time click on "Format only cells containing the specified value" and switch "Cell Value" to "Blank" in the field below.
After switching to blank, click the "Format" button.
When the formatting menu appears, "No Color" may have been selected from the beginning, but still click "No Color" again and then click the "OK" button.
Now copy the "formatting" to the rest of the cells with the formatting again as before.
The formatting is now complete. As you can see in the figure below, items in the list and blank items are "white," and only cells with "Other" that are not in the list are now filled in "yellow.
Conclusion
As mentioned above, if you use the VLOOKUP function or the INDEX/MATCH function to tabulate the data, it is not possible to tabulate the data well unless it is an exact match. Therefore, we always want the input value to be selected from a pull-down menu, but this may cause problems if there is no choice when inputting the data.
In such cases, it may be smoother to set up the system so that you can enter values that are not on the list and let them be shaded as in this case, and then make corrections later. Please choose the method that speeds up your work.
See this article for instructions on how to set up pull-downs.
To enable pull-down entry of items not on the list, please refer to this article.
Comment