Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reverse lookup? 2

Status
Not open for further replies.

cowski

Mechanical
Apr 23, 2000
8,155
US
I have a list of (hundreds of) colors with the numbers we have assigned to them such as:

100n Light red
101n metallic blue
102n transparent red
.
.
.

I created a lookup so that a user can type in a number to see what color it is assigned. I would now like to be able to type in a base color such as "red" and get a list of all possible color numbers (light red, met. red, bright red, etc, etc).

Is there a built in function(s) that I can use to do this? I am fairly comfortable writing VBA code, but I don't want to reinvent the wheel (unless of course there are lots of value added features and high margins - oops got caught up in the marketing spirit).
 
Replies continue below

Recommended for you

A pivot table would allow the data association to be reversed...

TTFN
 
IRstuff,
Thank you for your post. However, after looking through the help files and experimenting with a pivot table, I am unable to get the result that I would like. Assume the numbers are in column A and the descriptions are in column B. If the user types "red" into the input cell, I would like a list of all entries that have "red" somewhere in the description (say numbers in col C and descriptions in col D).

Thank you for the idea, pivot tables are new to me so it is quite possible that I am using it incorrectly. I will dig a little deeper into the help files to learn what I can. In the mean time any and all ideas are welcome!
 
Make a data base with numbers (column A) and colors (column B).
Highlight complete database and select Data/AutoFilter.
Dropdown arrows appear in each column.
Select the one on color and then select custom.
In box select equals to and then in the box to the right type either * red or red * depending on what you want sorted.
Alternately select ends with or begins with and then type red in second box.
 
Thank you melone and aviat! It works wonderfully. Stars for both of you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Top