Help With Formula

[MickB]

Hi,

On Sheet 1 under Column B, I have a list part numbers that are defective.
On Sheets 2, 3, 4, 5 etc...... under Column O, I have blank cells to enter part numbers - if a number is entered into any cell under Column O that matches any of the defective part numbers in Sheet 1 Column B I would like the adjacent cell in Column P to fill red (as a warning flag).

What formula would work?

Thanks Mick

 

[Toadstool]

Hi MickB,

This should work but you'll need to apply to column P on each sheet.

MickB.xlsx
	B
1	Defective Parts
2	A123
3	B234
4	C456
5	D888
6
Sheet1

MickB.xlsx
	O	P
1	Part
2	X555
3	Z838
4	A123
5	N777
6	C456
7	P020
8
9
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:P9999	Expression	=MATCH(O2,Sheet1!B:B,0)	text	NO

 

[MickB]

Sorry didn't work..... I should probably add that I am using an old version of excel (2007).

Cheers Mick

 

[Toadstool]

Mick,
I'm not sure if that's the problem as Microsoft says"

I don't have Excel 2007 available so let me put together a video to make sure you're following the right steps.
I'll be back in 30 minutes.

 

[Toadstool]

Mick,

OK, take a look at this 3 minute video and please tell me if these are the steps you followed.

 

[Fluff]

I'm pretty sure that you could only use CF formula referring to other sheets from 2010 onwards.
For 2007 you need to use a named range.

 

[MickB]

Thanks for the video, I have gone through the steps on the video and end up with the same pop-up message "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."
I am going to try the same from the beginning starting afresh.

Cheers Mick

 

[Fluff]

If you create a named range for the data in col B of sheet 1, you can then refer to that in the CF formula & it should work.

 

[Toadstool]

Thanks for the video, I have gone through the steps on the video and end up with the same pop-up message "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."
I am going to try the same from the beginning starting afresh.

Cheers Mick

Mick,

Did you see the comment from @Fluff about Named Ranges?

You could try this:

MickB.xlsx
	B
1	Defective Parts
2	A123
3	B234
4	C456
5	D888
6
7
Sheet1

MickB.xlsx
	O	P
1	Parts
2	TT33
3	B234
4	GGT8
5	D888
6	LLL9
7
8
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:P11	Expression	=MATCH(O2,Defective_Parts,0)	text	NO

 

[MickB]

I just keep getting.... "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."
Using =MATCH($B3,$D$3:$D$12,0) gets me closer to what I am trying to achieve but it only works within the same worksheet.
I am a complete novice with excel so I would benefit from start to finish instructions kept as simple as possible.
Cheers Mick