Need help with a formula in conditional formatting

DeanStor

New Member
Joined
Feb 18, 2019
Messages
3
I am looking at getting some help.
I have a spreadsheet, with items in it.
I need to write a formula in the conditional formatting, new rule section.

How do you instruct excel to automatically recognise specific words out of a linked list once any of them have been entered more than once, when input into the spreadsheet and upon recognition highlight specified words into a colour.

Thanks

DS
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
recognise specific words out of a linked list once any of them have been entered more than once,
countif() >1
where the range is your list of words and criteria is the first cell in the range

assuming your list is in Column D
and you enter in column A starting at A1
select A
conditional formatting rule

=Countif($D:$D, A1 ) > 1
 
Upvote 0
Thanks a lot,

I've tried that formula, It comes up with False.
It doesn't recognise that all 3 of the words selected are in the spreadsheet?
Could this be as I am using Data Validation with Shrinking List and drop downs list??

I can format individuals data but not multiply.

DS
 
Upvote 0
Thanks.
I'm sure it can be done, as I've seen similar done before.

Spreadsheet came from;
https://people.highline.edu/mgirvin/excelisfun.htm
241,

I have used this formula and edited it to create 5 columns to be selected from as per below.


I am using it to create a menu for my non skilled staff, I have done it this way so that they can't stick the same items on 3 days in a row. However I need it to highlight for instance if they have chosen more than 2 pre-fried products. as in my menu below (Fish, chips & wedges) by the schools food laws they are only allowed 2.

I have got this part of it working so the choices in the drop down boxes disappeared as and when they chose them, but they won't highlight for me.[TABLE="width: 1222"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Week 1[/TD]
[TD]Meat & Fish Main [/TD]
[TD]Vegetarian Main[/TD]
[TD]Starchy Carb[/TD]
[TD]Vegetables[/TD]
[TD]Dessert[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Chicken Pie[/TD]
[TD]Cheese & Tomato Pizza[/TD]
[TD]Chips[/TD]
[TD]Swede & Carrots[/TD]
[TD]Lemon Sponge with Custard[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]Salmon Pie[/TD]
[TD]Vegtable & Lentil Curry[/TD]
[TD]Drizzled Garlic Slice[/TD]
[TD]Savoy Cabbage[/TD]
[TD]Cinnamon Rice Pudding[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]Chicken & Mushroom Risotto[/TD]
[TD]Veggie Sausages[/TD]
[TD]New Potatoes[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]Meatballs in Tomato Sauce[/TD]
[TD]Root Veg Cottage Pie[/TD]
[TD]Penne Pasta[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]Battered Fish[/TD]
[TD] [/TD]
[TD]Baked Wedges[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Thanks again Dean...
 
Upvote 0
sorry dont fully understand
as in my menu below (Fish, chips & wedges)
which day is that ?

How does excel know which are fried items ? would need a lookup table or something to tell it that this item is fried
Are baked wedges considered Fried

so you want to highlight when 2 fried items are chosen on same day and if 3 of the same item are in 3 consecutive days
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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
Back
Top