IF A CELL ON ONE WORK SHEET MATCHES ANOTHER WORKSHEET

for instance if A2 on worksheet one = D6 on Worksheet 2 then I want A4 on
worksheet one to equal D10

Not sure if this is what you are looking for, but I would try:

=if((Sheet1!A2=Sheet2!D6),D10,"FALSE")

This would check if cell A2 in Sheet1 is equal to D6 in Sheet2, and if it
is, return the value that is in D10. If not, it will return the word FALSE.

Hope this helps!



"conditional format if cell contains word" wrote:

IF A CELL ON ONE WORK SHEET MATCHES ANOTHER WORKSHEET

for instance if A2 on worksheet one = D6 on Worksheet 2 then I want A4 on
worksheet one to equal D10

Hi,
In A4 enter

=if(A2=Sheet2!D6,D10,"")

I assume that if the result doesn't match you want blank in that cell

If this helps please click yes, thanks

"conditional format if cell contains word" wrote:

IF A CELL ON ONE WORK SHEET MATCHES ANOTHER WORKSHEET

for instance if A2 on worksheet one = D6 on Worksheet 2 then I want A4 on
worksheet one to equal D10

Thank you Amy, the formula worked for one cell and the next because they were
in order. However, the rest showed up FALSE. I THINK, this is because the
master list(worksheet 2) has more products on it then the order(worksheet 1).
So the master list is 1-10, but the order is only items 1,5, & 8 lets say. I
think the computer is trying to match 1 to 1, 5 to 2, and 8 to 3. So I guess
I would have to do something with telling it where to search for the item
code as some sort of index

"Amy Brooks" wrote:

Not sure if this is what you are looking for, but I would try:

=if((Sheet1!A2=Sheet2!D6),D10,"FALSE")

This would check if cell A2 in Sheet1 is equal to D6 in Sheet2, and if it
is, return the value that is in D10. If not, it will return the word FALSE.

Hope this helps!



"conditional format if cell contains word" wrote:

IF A CELL ON ONE WORK SHEET MATCHES ANOTHER WORKSHEET

for instance if A2 on worksheet one = D6 on Worksheet 2 then I want A4 on
worksheet one to equal D10

What columns have you got in each worksheet?

"conditional format if cell contains word" wrote:

Thank you Amy, the formula worked for one cell and the next because they were
in order. However, the rest showed up FALSE. I THINK, this is because the
master list(worksheet 2) has more products on it then the order(worksheet 1).
So the master list is 1-10, but the order is only items 1,5, & 8 lets say. I
think the computer is trying to match 1 to 1, 5 to 2, and 8 to 3. So I guess
I would have to do something with telling it where to search for the item
code as some sort of index

"Amy Brooks" wrote:

Not sure if this is what you are looking for, but I would try:

=if((Sheet1!A2=Sheet2!D6),D10,"FALSE")

This would check if cell A2 in Sheet1 is equal to D6 in Sheet2, and if it
is, return the value that is in D10. If not, it will return the word FALSE.

Hope this helps!



"conditional format if cell contains word" wrote:

IF A CELL ON ONE WORK SHEET MATCHES ANOTHER WORKSHEET

for instance if A2 on worksheet one = D6 on Worksheet 2 then I want A4 on
worksheet one to equal D10

The master looks like this but much larger.
ITEM NUMBER DESCRIPTION AVG Wght EST
4263 CHICKEN BREAST 10.0 P-7085
59234 CHICKEN DRUM 10.5 P-72
66850 TURKEY HAM 10.0 P-210
89222 SLICED BONELESS 25.0 13433

The order looks like this:
Item code Description Cases total
weight EST
143823 9
143836 10
143563 5
143875 6


ID CERT_DESC CASES total WEIGHT EST
143823 BEEF CHUCK EYE ROLL 9 585 86E
143836 BEEF FEET USDA 4 16 562M
143563 BEEF FLANK STEAK 2 130 86E


So I need the descriptions and ESTs to be filled in with exact matches and
need the weight to be the avg weight from the master multiplied by the cases
from the order.

Ok, I didn't quite understand the second sheet there, but from how I can
interpret it...
If you have the master as:
A B C
D
1 ITEM NUMBER DESCRIPTION AVG Wght EST
2 4263 CHICKEN BREAST 10.0 P-7085
3 59234 CHICKEN DRUM 10.5 P-72
4 66850 TURKEY HAM 10.0 P-210
5 89222 SLICED BONELESS 25.0 13433

and the order as:
A B C
D E
1 ID CERT_DESC CASES total WEIGHT EST
2 143823 BEEF CHUCK EYE ROLL 9 585 86E
3 143836 BEEF FEET USDA 4 16 562M
4 143563 BEEF FLANK STEAK 2 130 86E

I would add a column between ID and CERT_DESC for the Item Code, then use a
vlookup in the CERT_DESC column:
=vlookup(OrderSheet!$B2,MasterSheet!$1:$65536,2,0)
---------------------------------------------------------------
Whe
OrderSheet! is the name of the sheet with the orders on (remember to include
the ! after the sheet name)

$B2 is the item code you want to look up

MasterSheet! is the name of the master sheet (again, don't forget to include
the !)
$1:$65536 is basically including the whole sheet to search (in Excel 2003)

The 2 is the column number (in this case you are looking up the Description,
so in your sample that is the second column)

The 0 refers to whether you need an exact match. If you need the item codes
to match exactly, keep it as 0, if you want to search for the most similar
item code, change it to 1.
-----------------------------------------------------------
The same would apply for the EST column, although the column number would
instead be 4, because in the master sheet sample, EST is the fourth column.
--------------------------------------------------------------------
For the total Weight column, I would use:
=(vlookup(OrderSheet!$B2,MasterSheet!$1:$65536,3,0 )*C2

The vlookup bit is nearly the same, but we have again changed the column
number to match the Weight column.
I have placed the vlookup in brackets, so that at the end we can multiply it
by the number of cases.

Hope this is what you are looking for!


"conditional format if cell contains word" wrote:

The master looks like this but much larger.
ITEM NUMBER DESCRIPTION AVG Wght EST
4263 CHICKEN BREAST 10.0 P-7085
59234 CHICKEN DRUM 10.5 P-72
66850 TURKEY HAM 10.0 P-210
89222 SLICED BONELESS 25.0 13433

The order looks like this:
Item code Description Cases total
weight EST
143823 9
143836 10
143563 5
143875 6


ID CERT_DESC CASES total WEIGHT EST
143823 BEEF CHUCK EYE ROLL 9 585 86E
143836 BEEF FEET USDA 4 16 562M
143563 BEEF FLANK STEAK 2 130 86E


So I need the descriptions and ESTs to be filled in with exact matches and
need the weight to be the avg weight from the master multiplied by the cases
from the order.

Ok, I've just noticed not everything really fit on there...

A is supposed to be Item Number
B is supposed to be Description
C is supposed to be Avg Weight
D is supposed to be EST

A is supposed to be ID
B is supposed to be CERT_DESC
C is supposed to be CASES
D is supposed to be Total WEIGHT
E is supposed to be EST


"Amy Brooks" wrote:

Ok, I didn't quite understand the second sheet there, but from how I can
interpret it...
If you have the master as:
A B C
D
1 ITEM NUMBER DESCRIPTION AVG Wght EST
2 4263 CHICKEN BREAST 10.0 P-7085
3 59234 CHICKEN DRUM 10.5 P-72
4 66850 TURKEY HAM 10.0 P-210
5 89222 SLICED BONELESS 25.0 13433

and the order as:
A B C
D E
1 ID CERT_DESC CASES total WEIGHT EST
2 143823 BEEF CHUCK EYE ROLL 9 585 86E
3 143836 BEEF FEET USDA 4 16 562M
4 143563 BEEF FLANK STEAK 2 130 86E

I would add a column between ID and CERT_DESC for the Item Code, then use a
vlookup in the CERT_DESC column:
=vlookup(OrderSheet!$B2,MasterSheet!$1:$65536,2,0)
---------------------------------------------------------------
Whe
OrderSheet! is the name of the sheet with the orders on (remember to include
the ! after the sheet name)

$B2 is the item code you want to look up

MasterSheet! is the name of the master sheet (again, don't forget to include
the !)
$1:$65536 is basically including the whole sheet to search (in Excel 2003)

The 2 is the column number (in this case you are looking up the Description,
so in your sample that is the second column)

The 0 refers to whether you need an exact match. If you need the item codes
to match exactly, keep it as 0, if you want to search for the most similar
item code, change it to 1.
-----------------------------------------------------------
The same would apply for the EST column, although the column number would
instead be 4, because in the master sheet sample, EST is the fourth column.
--------------------------------------------------------------------
For the total Weight column, I would use:
=(vlookup(OrderSheet!$B2,MasterSheet!$1:$65536,3,0 )*C2

The vlookup bit is nearly the same, but we have again changed the column
number to match the Weight column.
I have placed the vlookup in brackets, so that at the end we can multiply it
by the number of cases.

Hope this is what you are looking for!


"conditional format if cell contains word" wrote:

The master looks like this but much larger.
ITEM NUMBER DESCRIPTION AVG Wght EST
4263 CHICKEN BREAST 10.0 P-7085
59234 CHICKEN DRUM 10.5 P-72
66850 TURKEY HAM 10.0 P-210
89222 SLICED BONELESS 25.0 13433

The order looks like this:
Item code Description Cases total
weight EST
143823 9
143836 10
143563 5
143875 6


ID CERT_DESC CASES total WEIGHT EST
143823 BEEF CHUCK EYE ROLL 9 585 86E
143836 BEEF FEET USDA 4 16 562M
143563 BEEF FLANK STEAK 2 130 86E


So I need the descriptions and ESTs to be filled in with exact matches and
need the weight to be the avg weight from the master multiplied by the cases
from the order.