New Project, Different Problem: AutoFilter?

[Guest]

I'm creating a database of outside trainers. Each trainer can handle
multiple categories of training. It is essential that the training manager
be able to create lists for specific categories as the need arises, as well
as being able to do more standard sorts such as Name. Rather than making
multiple entries for each Trainer (one for each category, which may reach
10+), I'd like to allow multiple entries in the Category column utilizing a
key for each category.

It seems that AutoFilter would be the ideal method for creating this db.
Well, actually Advanced AutoFilter, since I'll need to be able to select for
a specified category key.

I've begun the research in how to use this feature, but before I go much
further I'd like to make sure I'm on the right path.

So -- would Advanced AutoFilter allow me to extract those records where a
search criteria is met?

-- would I use the criteria range formula Trainers!I3:I70="10" to
extract those records in Trainers where Column I contains the category key
"10"?

-- assuming the above is true, is there a better source for direction
than Contextures?


Thanks so much!

 

[Debra Dalgleish]

You could use the Custom option in an AutoFilter to find records that
contain a 10.

Or, you could use an Advanced Filter, with the Column I heading, and the
criteria: *10

However, you may encounter problems with either technique if your course
numbers are similar. For example, filtering for *5 would return 1, 15,
25, 50, etc. You might be able to work around this by adding a text
character at the start and end of each course number.

And there may be better sources for direction than Contextures. What
information are you looking for?

 

[Guest]

And there may be better sources for direction than Contextures. What
information are you looking for?

I went to Contextures for a clearer understanding of how to use the Advanced
AutoFilter component. Which I think it did provide. However, if there's
something online that would provide better/clearer instruction and
explanation, I'm open to suggestions.

And thanks for the heads-up re potential problem on multiple returns because
of embedded numbers. I'll have a look at my categories and see if I can do
something to set apart the 1 from the 10. I believe I read something at
Contextures about a way to expressly identify a 10 vs a 1.

Helen

 

[Debra Dalgleish]

There are a other instructions and examples in Excel's Help -- you might
find those useful. Also, if you can use code, there's a sample workbook
here:

http://www.contextures.com/excelfiles.html#Filter

under the heading: Filter Rows for Text String

It uses an advanced filter, and event code, to filter a list for a text
string. You may be able to adapt this to your workbook.

You may be remembering the example in which the criteria is: ="=Ice"
to prevent the return of "Ice cream"

However, it sounds like your course numbers could be in any order in the
cell. One trainer might have: 3, 5, 12, 20
Another might have: 5, 20, 25
Those wouldn't work with the above criteria example.

 

[Guest]

Thanks, Debra. It's good to know I'm at least on the right path. I can't
use code, but I will spend some time with the Contextures site and see if I
can figure this thing out.

 

[Debra Dalgleish]

If you get stuck, you can post another message here, and someone may be
able to help. And if you have any suggestions for adding to the Advanced
Filter material on my web site, let me know.