Relating multiple columns in two spread sheets

**Cemt5462** · 09-01-2011, 10:03 AM

I am having problems with my EMS inventory. I am trying to get my field inventory item number column in SS#1 to match up with my item number columns in SS#2.

I have attched my two sheets that I need to combine for inventory comparison. SS#1 is my inventory sheet from the field. The item number (column A) is the same as item number column in SS#2 (column E). I would like to have my SS#2 Column E & K match up with the corresponding Item Number Column in SS#1. This way I can match what my inventory they send me from the field to what I have in my system. Is there a formula that will allow me to do this?

i have attached my two SS.

**JBeaucaire** · 09-01-2011, 10:27 AM

1) open both workbooks.

2) in the TRSTKMD003 sheet, unlock column G270:

=VLOOKUP($A5, [CDUGAS_TSIQRYPROD_INV_TRUCK_090111_082350.XLS]Sheet1!$E:$K, 7, 0)

3) The qty for the items that exist in the other sheet will appear, the items that are not found will say "number not available" or "#N/A".

4) Now that this working, you can close the other workbook, your sheet will update the formulas to include the full path to the workbook and keep working even though the sheet is closed.

5) Now you can save the main workbooks and continue to use normally, the reference values will always appear based on the values currently saved in the other workbook. They will update when that workbook is updated.

**Cemt5462** · 09-01-2011, 12:02 PM

WOW! Thank you. Maybe you could help me on a couple of other problems im having. I am trying to get the difference between my numbers in column E and column G so that i know in column H if there are any diferences. And if possible to color code them ( Say yellow if the number in column E is more than column G, and red if the number is less in column E than in G.

The last problem is that I would like to format the exp. date column so that if the date is 60 days from today it appears yellow and if it is past todays date it appears red. I tried this in conditional formatting and it ends up coloring all the boxes. I am trying to only color in the date with a 60 day to expire and a past expire.

**JBeaucaire** · 09-01-2011, 12:38 PM

You can use conditional formatting for this. Highlight G5:G270 and apply these settings:

Condition1: Formula Is: =$G5>$E5
Format... Pattern: Red

Condition2: Formula Is: =$G5<$E5
Format... Pattern: Yellow

Next, select F5:F270 and apply these conditional formatting settings:

Condition1: Formula Is: =AND(F5>0,F5<TODAY())
Format... Pattern: Red

Condition2: Formula Is: =AND(F5>0,F5<TODAY()+60)
Format... Pattern: Yellow

**Cemt5462** · 09-01-2011, 12:49 PM

Thanks. Im having a issue with the expiration date column. each time I put in a date it appears as a 5 digit number. (ex. I put 8/31/11 and when I click off it appears as 40786)

**JBeaucaire** · 09-01-2011, 05:05 PM

You will, of course, need to format that column as Date format.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.