Compare data on 2 separate worksheets and pull a reference cell

**DWAYNE64** · 03-14-2009, 04:36 PM

I have on inventory sheet that has all the data in each cell 2612 to be exact! That changes month to month with deletions.

The format is: Sheet 2
A/ B/
334702/ UEMR88QX
334703/ UEMR85QX
334704/ UEMR81QX
334707/ UEMR8JQX

The master worksheet has about 5000 items and the (A and B) data are both on it with other data ranging from (A-Y). My question is how do I have a cell look up data and return that it exists or doesn’t exist on the inventory sheet?

Master
A/ B/ C/ D/ E/
35/ 465/ 881676311350/ 311350/ UEMR8ZTU
36/ 469/ 881676310722/ 310722/ UEMR8V5V
37/ 483/ 881676310924/ 310924/ UEMR8XYU
38/ 805/ 881676331096/ 331096/ UEMR8J13

My other problem I need to take that months inventory list and have it look at the master list and return the system number from the master list (B) next to the matching inventory number (C). Allowing me to cross match inventory to master each month and save inventory worksheet each month.

I know I can copy the column and past is as a value so I won’t lose the numbers as the master changes but getting the data onto that months inventory has been a pain.

**Steve R** · 03-14-2009, 07:09 PM

Your first question: Do you want to look up both A and B data on your Master sheet and see if the data exists on your inventory list? Must both A and B exist on the same row on your inventory list?

**DWAYNE64** · 03-14-2009, 07:54 PM

This is a scaled down version of the inventory, my issue is being able to cut and past the formula from one month to the next and have each master update from the invoice and save the prior month data as it was updated. I plan to do this by just pasting the column as a value to keep the numbers.

My issue is linking the cells on each of the master documents to the invoice?

I can cut and past new formulas I just have know idea how to do the link?

The system numbers in orange need to make it to the inventory
The green on the master list will be the link from the inventory

I know its a mess!

**Steve R** · 03-16-2009, 07:59 AM

I created a new column (N) on sheet INVOICE 0812. It checks if the values on column B and C exists on the other sheets (Master, Master 2 and Master 3)

See attached workbook.

**DWAYNE64** · 03-16-2009, 09:46 AM

This worked great!!!! it will help me tack down the numbers that are on each invoice!

Is it possible to do the same in reverse and have it pull the system number from each master list and auto fill it into the Invoice?

Or is that going to take a macro?

This fix has just taken about 2 days out of my life each month checking numbers!

Thanks so MUCH!

**DWAYNE64** · 03-16-2009, 05:16 PM

I figured out a few things but still have been unable to get the Cell in the invoice to pull the matching system number after a match is found with the CCSD and what ever master sheet it was found on?

After messing around I noticed that I needed to have the array function on each cells function! {} and that to keep the numbers from changing on the search field I had to use the $A$1:$A$400 this little bit frustrated me after multiple attempts to write the function over.