Query from other sheet, Conditional Formatting

[erjfly2013]

Hello,

The scenario:
My company ships hazardous materials. We use a variety of shipping companies (i.e. fedex / ups) who offer a host of service levels (ground, overnight) . Not all of our hazardous products are able to be transported by every shipping service level. To see if a product is able to be transported via a certain shipping service, I have a simple table set up listing each product in rows, and shipping service in columns. When crossed, a green or red cell indicates that this scenario is acceptable, or not acceptable. My list of products is somewhat extensive (~100) with about 20 service levels, and therefore this table is inconvenient to use.

I would like to have a cell where i enter a product (part#) and a shipping service (3-digit code), a third cell would result with "Compatible" (green fill) or "Not Compatible" (red fill).

Ideas?

[jeffreybrown]

My idea...a sample file would help. Does not have to be the whole table, but a good representative sample.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[erjfly2013]

HazMat Conpatibility by Carrier sample.xlsx
Here is what my sample file looks like so far.

I would like to have a cell (or two) where i query the item number (ex: 100 series rows) and a shipping service (200 series columns) and have it display whether or not the scenario works.

In my sample, 101/201 is green. 101/205 is red. If i were to enter 101 and 201 somewhere, it should tell me it works. Or 101 and 205 would say it does not work.

[FDibbins]

without VBA, if you used 1 or 2...or whatever you want, to show what works, you could use index/match to find what you need. 2007 does not have a function/formula to identify colors (as far as i know)

for instance, in B19, i entered 105 and in C19, i added 201
in B20, i used this
=INDEX($B$2:$P$18,MATCH(B19,A2:A18,0),MATCH(B1:P1,C19,0))
it gave me 1 for "ok"
changing B19 to 106 gave me 2 for "not ok"

[erjfly2013]

Excellent!

But I can only get this to work for tests in column b. If, for example, we change C19 to anything besides 201 (202 or 203), we get #N/A.

[erjfly2013]

Edit:

I changed the formula as follows to test any cell in any column:
=INDEX($B$2:$P$18,MATCH(B19,A2:A18,0),MATCH(C19,B1:P1,0))

Thank you very much, this is exactly what I need!

[erjfly2013]

Excellent!

But I can only get this to work for tests in column b. If, for example, we change C19 to anything besides 201 (202 or 203), we get #N/A.

[FDibbins]

was your last post a duplicate? if so...

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)