VLOOKUP using two worksheets?

[Rich K.]

Using Excel 2003: I have a worksheet that I have to update daily from data
that is created by an SQL query for orders created and not sent. I have to
distinguish between production and test vendors. I keep a separate worksheet
that has the vendor number & description and status. Once I update the first
spreadsheet with the SQL data, I would like to run something using the values
in my second worksheet that would change the background color of the test
vendors, parallel vendors, etc. in the first spreadsheet based on the
vendor/status in the second.

Here are the exact particulars: In the "POs Unsent 2007", the vendor number
is in column D, with anywhere from 1 to 2500 rows. In the "EDI Status
Report", the vendor number is in column A and status is in column C, with
anywhere from 1 to 50 rows.

Hope this explains it and many thanks for any and all help.

 

[squenson via OfficeKB.com]

In the second row of "POs Unsent 2007", in a new column, let's say Z, type:
=VLOOKUP(D2, 'EDI Status'!A1:C50, 3, 0)
This should retrieve the status of the vendor on the current row.

Then select the whole range A2:Z2500 and create a conditional formatting
(menu Format > Conditional Formatting) with "Formula Is" |=$Z2="Test"| (don't
type the "|" characters), and format the cell when the condition is true. Add
up to two other conditions for the range, simply change "Test" by your
different statuses. Need more than 3? A macro will be needed!

Stephane Quenson