Help With Formula

MickB

New Member
Joined
Oct 21, 2020
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
Hi MickB,

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

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


MickB.xlsx
OP
1Part
2X555
3Z838
4A123
5N777
6C456
7P020
8
9
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P2:P9999Expression=MATCH(O2,Sheet1!B:B,0)textNO
 

MickB

New Member
Joined
Oct 21, 2020
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
Sorry didn't work..... I should probably add that I am using an old version of excel (2007).

Cheers Mick
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
Mick,
I'm not sure if that's the problem as Microsoft says"
1608503291963.png


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

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Mick,

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 21, 2020
Messages
6
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
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
1Defective Parts
2A123
3B234
4C456
5D888
6
7
Sheet1


MickB.xlsx
OP
1Parts
2TT33
3B234
4GGT8
5D888
6LLL9
7
8
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P2:P11Expression=MATCH(O2,Defective_Parts,0)textNO
 

MickB

New Member
Joined
Oct 21, 2020
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,394
Latest member
mahendar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top