Hello Jharmke,
OK. I will provide a few different methods, including one that uses a
dialog box so you can have an interface similar to that used in
Microsoft Works for Macintosh.
In these examples, I assume the data has the following characteristics:
- the first row of information has a title describing the field in
the column below it
- each row is a record of information
- the information is bounded by at least one blank row and column
You can check the range of cells affected by these solutions by doing
the following steps:
- Select a cell within your data (any will do)
- Select the Edit -> Goto menu item, a dialog box will appear
- Select Special..., another dialog will appear with a set of selections
- Select Current Region, then OK.
The top left cell will be selected and a light blue highlight should
appear over all your data.
[1] Autofilter
This is perhaps the most simple method to set up and is quite
powerful. To set it up:
a. Select a cell within your data.
b. Select the Data -> Filter -> Autofilter menu item
At this point, the right side of each header cell will have a little
box with up / down arrows in it.
c. Click on one of those boxes and a menu will pop up giving you a
number of options; select Custom Filter..., a dialog box will appear.
It should say something like "Show rows where: (name of field)" at the
top and have a pair of menu selections and fields. The default type of
criteria is equals and the value is blank. Based on what you are
looking for:
d. Using the menu selection, change the criteria to contains and
enter 1234 as the value. Select OK.
At this point Excel should display only the rows where 1234 is part of
the field selected.
You can filter on more than one field by applying more than one custom
filter. This also allows ranges of values. For example, you could have
Title contains 1234
Price greater than or equal to 100 AND less than or equal to 200
which will display rows meeting both criteria.
You can do the first two steps (a and b) once, and then as you get
each phone call do the last two steps (c and d) for the searching you
need to do.
[2] Advanced Filters
There are some combinations that cannot be done with autofilter. For
example, if you want to select records based on three different names,
then Autofilter won't do. In this case:
Add a few rows, perhaps above your data so it will be at the top of
the spreadsheet. In this area:
- In the first row, enter the names of the fields you want to filter on
- In each following row, enter the values you want to filter on.
- Select menu item Data -> Filter -> Advanced Filter
a dialog box should appear with three fields and a pair of buttons at
the top titled "filter the list, in place" and "copy to another
location". The three fields are as follows:
- the first field should select the range of data to be filtered.
- the second field should select the filter criteria (don't select
any extra lines)
- the third field should select where to copy the data if you don't
filter in place
There is an additional explanation of criteria in the on line help,
but a few examples should illustrate the concept.
Title
*1234*
will match all records which have 1234 as part of the "Title".
Cost Cost
>100 <200
will match all records where the cost is greater than 100 and less than 200
Title Cost Cost
*1234* >100 <200
will match all records where the Title contains 1234 and the cost is
within the range.
Title Cost Cost
*1234*
>100 <200
will match all records where the Title contains 1234 OR the cost is
within the range.
Another advantage of this method allows you to make the record
matching a permanent part of the spreadsheet at the top of the data.
I consider this harder to set up and use than the other methods, but
it provides for more powerful searches if you need it.
[3] Adding a selector / buttons
Something similar in look to a dialog box but part of the sheet is to
reserve a couple cells near the top to enter the value to select on
and a pair of buttons. I would set up something like
Model Number
1234
with a pair of buttons to the right. If Model Number is in cell A1,
1234 is in cell A2, and the data starting in cell A4 (and subsequent
rows), you can set it up like this.
a. Enter Model Number in A1
b. Leave A2 blank until you are ready to do the search.
c. Menu item View -> Toolbars -> Forms
d. The button item is the second item on the right. Click on it.
e. Click and drag over a blank area (a couple cells will do) and a
dialog box will appear. Set the Macro name of the first one to
FilterForm, and OK.
f. Select the text in the button and change to Search, Match Records,
or whatever you want.
g. Do a second button (steps d, e, f) and set the macro name to
ShowAll and the name to match.
h. Menu item Tools -> Macro -> Visual Basic Editor
If you don't already use macros, use
i. Menu item Insert -> Module
to create a module
j. Copy / paste the following into a module.
Sub FilterForm()
'
' FilterForm Macro
' Macro recorded 2/3/2004 by Maniac
'
'
Range("A4").Select
Selection.CurrentRegion.Select
Selection.AutoFilter
a$ = Format(Range("A2").Value, "0")
Selection.AutoFilter Field:=1, Criteria1:="=*" + a$ + "*", Operator:=xlAnd
End Sub
Sub ShowAll()
'
' ShowAll Macro
' Macro recorded 2/3/2004 by Maniac
'
'
Range("A4").Select
Selection.AutoFilter
End Sub
If you need a different column, change the Field:= value to the correct column.
I suggest saving the spreadsheet with the buttons / macros at this point.
k. Excel -> Quit and return to Microsoft Excel
l. File -> Save
and the new spreadsheet will have the search tool saved as part of the
overall spreadsheet.
At this point, you should be able to enter 1234 into A2 and click on
the Search button to select just the lines with 1234 in the column
referenced in the macro. You can click the Show All button to reset
back to the unfiltered data.
Another suggestion - not specific to searching for data, but will keep
the search capability on the screen. I find this especially helpful to
keep the column titles (and row titles) on the screen when I have a
large spreadsheet.
At the top right (above the vertical scroll bar) and bottom right
(right of the horizontal scroll bar) is a tool you can use to split
the worksheet horizontally and vertically. On my system it looks like
a gray oval (or blob). Click and drag the upper right one down a
couple rows to split the screen horizontally. The part above the split
will be the part always on the screen.
If you have a wide spreadsheet and want to keep a couple columns on
the left visible, click and drag the lower right one to the left.
Then do
Window -> Freeze Panes
to lock the lines you have displayed at the top (and left).
If you make a mistake or want to remove this feature, use
Window -> Unfreeze Panes
or
Window -> Remove Split
to remove the frozen panes.
To help find the information needed for this answer, I searched
Excel's online documentation using phrases such as:
filter
match records
freeze panes
button
field
for basic Excel command operation. For the macro, I recorded the basic
steps I wanted to use and then modified it using information from the
Object Browser, Immediate Window (for trying actions), and the Visual
Basic help to look up information.
One (or more) of these methods should give you the ability to look up
and view all the documents with 1234 (or any search string) within it.
If you have ANY problems with one or more of these or if any part is
unclear, please request a clarification. I am glad to help further if
needed.
--Maniac |
