Search | Conditional search

Top  Previous  Next

This command selects all the cells within the current selection that match conditional criteria chosen by you.

You can choose between three types of conditional search criteria:

numerical cell values can be compared to threshold values;

cell values can be searched for a particular string;

cell formulas can be searched for a string.

Why would you want to use it?

There are numerous occasions when you might want to perform a search which is more complex than allowed for by Excel’s built-in functions:

you may want to search for values within a particular range, say all the cells that contain values between 90% and 100%;

conversely, you may want to exclude a range of values and find all the cells whose values are not between 25 and 50;

you may want to find all cells whose values exactly match a certain figure, or all cells which don’t match a particular value;

you may want to find all cell values which are greater than or less than a certain threshold figure;

you may want to find all the cells which contain a certain text string, say “input”;

you may also want to search all the cell formulas for a particular text string, say “IF”, “SUM”, or “TRUE”.

How to use it

Select a range of cells and choose the option 'Conditional' from the 'Search' pane; Choose one of the two  following search types from the first drop-down box:

Cell Value;

Cell Formula.

If you are performing a numerical search (‘Cell Value’), choose one of the following twelve options:

is between;

is not between;

equals;

not equals;

greater than;

less than;

greater than or equal to;

less than or equal to.

starts with

ends with

does not contain

contain

Type in a value or text in the text box below the chosen condition.

If you are performing a Cell Formula search, choose on the following options:

equals;

not equals;

starts with

ends with

does not contain

contain

Finally, click the ‘Search’ button.

The command will either select those cells within the current selection that match the selected criteria or return a “No cells in selection match conditional criteria” message.

WRINKLES

All of OAK's Search selection commands follow the One-cell rule.

The OAK | Search | Conditional search command resembles an amalgam of two of Excel's built-in commands.  It is like Excel's

Home | Editing | Find & Select | Find command, in that it looks for cell contents specified by the user

Home | Editing | Find & Select | Goto Special command, in the way it presents the results as a selection of potentially many areas.

What you could do if you didn't have OAK

If you did not have OAK, you would have to do any searching using Excel’s Home | Editing | Find & Select | Find command.  Searching for cells in which its value or formula equals or contains a known threshold value or text string can be achieved by Excel’s Find command, shortcut: Ctrl+F, and clicking on ‘Options’ button toward the bottom right of the dialog box that appears.  

Enter the threshold value or text string required in the ‘Find what’ text box and check ‘Match case’ and ‘Match entire cell contents’ as necessary.  The ‘Within’ drop-down menu will allow the user to perform the search either within the worksheet or within the workbook.  The ‘Look in’ drop-down menu allows the user to choose whether the search is performed on the cell formula, cell value or cell comment.  Clicking on ‘Find All’ will generate a list of links to cells which match the criteria. Repeatedly clicking on ‘Find Next’ will cycle through the cells identified that match the search criteria.  

Confining the search to the worksheet will make Excel perform the search subject to the One-Cell rule.  Therefore, if more than one cell is initially selected and this search is performed on the worksheet, then Excel will only identify the cells within the initial selection which match the criteria. In this case, Excel has not actually performed the search on the entire worksheet which could lead to error.  When performing this search on the entire workbook, Excel identifies all cells matching the search criteria within the workbook regardless of the initial selection.  

The OAK conditional search command offers the advantages

of being able to perform a “Cell Value is between…” conditional search which cannot be performed using Excel’s built-in functions

that the cells matching the search criteria are identified and selected all at once as result of the conditional search whereas the search as performed by Excel’s built-in functions will only enable the user to select the identified cells one at a time.