Takeaway: This illustrated walk-through (with a demo worksheet) shows how easy it is to create a multi-column search solution using validation lists and conditional formatting.
First, you’ll create a unique list of values based on the data you want to search. Next, you’ll use the data validation feature to create drop-down lists based on those unique lists. Once all the pieces are in place, you’ll add a conditional formatting rule that pulls them together.
Because this technique derives lists using the data validation feature, save this technique for static (or mostly static) data. You’ll have to update the lists and conditional formatting range if you change the data range. Of course, you could create dynamic lists and a dynamic input range to handle frequent updates — but that’s more work.
Note: You can download an Excel demo file that illustrates this technique.
1: Define the search
The first step is one of thought; you must define the search requirements. For this example, we’ll create a two-column search based on the submitted date and status in the sheet shown in Figure A. (The data is from the Purchase Order table in the Northwind Access database application. I changed a few of the status values to add more contrast.) The search will highlight submitted or approved orders for a specific date.Figure A
Determine your search requirements.
2: Make room
Adding the search drop-down lists above the data makes the most sense, most of the time, but it’s not a requirement. To add a few blank rows above the data range, select rows 1 through 3. Then, on the Home tab, choose Insert Sheet Rows from the Insert drop-down in the Cells group. In Excel 2003, choose Rows from the Insert menu.3: Create a unique list for each search column
This technique uses a data validation list based on the natural data, so you’ll need a list of unique values for each search column. First, create a list of unique dates from the Submitted Date column, as follows:- Select the data you want to list. In this case, that’s D4:D32.
- Click the Data tab and then click Advanced in the Sort & Filter group. In Excel 2003, choose Filter and then Advanced Filter from the Data menu.
- Select the Copy To Another Location option.
- Enter H4 as the Copy To range
- Select the Unique Records Only option, as shown in Figure B. (Search! is the sheet name identifier; I used the click method, so Excel is showing complete and absolute references.)
- Click OK. Figure C shows the resulting list next to the data.
Figure B
These settings will create a unique list of Submitted Data values.
Figure C
The resulting list is easy to see next to the data range.
You can copy the list anywhere, but to simplify the example, copy it close to the data range. When applying this to your own work, you might want to copy the list to another sheet so it doesn’t distract users.Repeat the process to create the second list, shown in Figure D. This time, the list range is E4:E32 and the Copy To range is I4. (The border between the data range and the two lists isn’t required; it simply visually separates the lists from the data range.)
Figure D
You’ll need a unique list for each search column.
4: Add descriptive labels for each validation list
You’re ready to add the validation lists, but first add labels to identify them. Just copy the header text for each search column to the corresponding cell in row 1 (D1 and E1), as shown in Figure E.Figure E
Set up the validation lists.
5: Create a drop-down list for each search column
Next, add a drop-down for each search column. We’ll base the Submitted Date list on the list in column H, as follows:- Select D2 — that’s where you’ll display the drop-down list, just below the descriptive text in D1.
- Click the Data tab and then choose Data Validation from the Data Validation drop-down in the Data Tools group. In Excel 2003, choose Validation from the Data menu.
- On the Settings tab (the default), choose List from the Allow drop-down.
- Specify =$H$5:$H$11 as the source, as shown in Figure F.
- Click OK.
Figure F
Specify the unique list in column H as this drop-down’s source.
Repeat the process to create a second list in E2, using =$I$5:$I$6 as the source. Figure G shows the resulting validation lists.Figure G
You just created two drop-down lists using the data validation feature.
6: Add the conditional formatting rule
All the pieces are in place. Now it’s time to add the conditional formatting rule:- Select the data range. In this case, that’s A5:G32.
- Click the Home tab and choose New Rule from the Conditional Formatting drop-down in the Styles group. In Excel 2003, choose Conditional Formatting from the Format menu.
- Select the Use A Formula To Determine Which Cells To Format option in the top pane. In Excel 2003, choose Formula Is from the Condition 1 control.
- Enter the formula =AND($D$2=$D5,$E$2=$E5), as shown in Figure H.
Figure H
This AND operator combines two conditions.
Using the AND operator, you can specify more than one condition. The first expression, $D$2=$D5, returns True when a value in column D equals the selected item in the drop-down list in D2. Notice that the reference to D5 uses mixed referencing to accommodate all the values in the data range. The second expression, $E$2=$E5, performs similarly for column E’s drop-down. When both expressions are True, the conditional formatting rule is true and Excel applies the conditional format, which we’ll specify next.7: Specify the conditional format
To continue, click the Format button and from the Fill tab, specify a banding color. Then, click OK to return to the original dialog, shown in Figure I, which displays the formula and the format. Click OK to return to the sheet.Figure I
This rule will apply the specified format when the conditional rule is True.
8: Try it out!
The search solution is ready to test by choosing an item from both lists. If the date in D2 returns a numeric value instead of a date, apply the Short Date format to D2. Figure J shows the result of choosing 3/24/2006 and Approved. As you can see, the conditional formatting rule highlights those records submitted on 3/24/2006 with an approved status. It’s amazing how much you get for such little effort!Figure J
Choose an item from each list to trigger the conditional formatting rule.
At this point, you might think you’re done — but you can easily expand this technique to include several search columns.9: Add another search column
Let’s suppose that you want to expand the search by highlighting records for specific personnel. In this case, you’d repeat steps 3, 4, and 5 to generate a unique list and a third drop-down. Use the settings shown in Figure K to add column C to the search solution. Then, apply the settings shown in Figure L to add a validation list to C2. Figure M shows the results of adding a third search column to the solution.Figure K
Use these settings to create a unique list of values from the Created By column.
Figure L
Use these settings to create a third drop-down.
Figure M
Add a validation list for the new search column.
10: Update the conditional format rule
You’re just about finished. Add a third expression to the conditional format rule, as follows:- Select the data range (A5:G32).
- Click the Home tab and choose Manage Rules from the Conditional Formatting drop-down in the Styles group. In Excel 2003, choose Conditional Formatting from the Format menu.
- Select the appropriate rule and click Edit Rule, as shown in Figure N. (There’s no manager in Excel 2003, just edit the rule appropriately.)
- Position the cursor between the last 5 and the closing parenthesis in the formula.
- Add a comma and the expression $C$2=$C5, as shown in Figure O.
- Click OK twice.
Figure N
Use the Rules Manager to change an existing rule.
Figure 0
Add the expression that adds column C values to the search.
Now you can search by three columns at once. Figure P shows the result of choosing Nancy Freehafer from the new list.Figure P
Postingan
