So I'm pretty new at this so I don't know if my formula is incorrect or if its something you just can't do with conditional formatting. In short I'm try to create a formula where if the value of two different cells drops below 1 another cell will turn yellow. The formula I'm using is:
IF(AND(B2=0,OR(C2=0,D2=0)))
Essentially if B2 is 0 and either C2 or D2 is 0 then A2 will turn yellow. so far I'm having no luck with this working. Any help will be greatly appreciative.
The If is implicit in conditional formatting
try =AND(B2=0,OR(C2=0,D2=0))
as CF after selecting the cell it is to be applied to
If it is to be applied to a range, referencing would change
Pepe thanks for your response. I tried using the formula you shared and it still does nothing.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Ok, so i attached the file I'm working on. Currently only have the formula for the first row, and it's the current one that you shared. just trying to do an inventory spreadsheat that will highlight the name of the tool when certain conditions are met so I know when to order more
There was no conditional formatting rules defined in your uploaded file.
Select A2:A29
On the ribbon: Home > Conditional Formatting > New Rule > Use a formula
Enter Pepe's formula:
AND(B2=0,OR(C2=0,D2=0))
Format with yellow fill
I have attached your file with the above mods.
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
Not sure why it didn't have the formula. I added the formula and saved it. That's wierd. Anyways I downloaded the file you uploaded and its working just fine. Not sure what was going on with my file. Thank you very much for the help
No problem - glad it's working for you now - thanks for the feedback.
Ok guys, I'm back. So I took the file that Geoff made and I've added a bit more too it. However, one thing I have noticed with that formula is that regardless C2 or D2, as long as B2=0 it will always change the yellow. I've tried looking at articles online and changing things up but it never seems to function the way I want so I'm coming here again for help. What I'm trying to do is have the formula check to see if C2 or D2 is 0 then if so check to see if B2 is 0. If both those conditions are met then the other cell (A2) would change yellow. As it stands for some reason it doesn't care about C2 or D2 even though those values are listed in the formula. So right now I'm kind of at a loss. Currently I I'm using the formula that Pepe wrote.
And which cells should the CF apply to?
@vanillagorilla: There's no Condtional Formatting on the sheet.
You've said you want to apply a condtional format to A2
if B2 is 0 and either C2 is 0 or D2 is 0
so
Select A2
Conditional Formatting
New Rule
Use a formula to determine...
=AND(B2=0,OR(C2=0,D2=0))
format as yellow
This works.
If this is not what you want then you need to explain more clearly.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Ok so before I work on why something isnt working I need to figure out why when I save my file the conditional formatting isn't saving.
UPDATE: So I'm not sure of what was causing the formula to not work correctly and I'm also not sure why my CF was not saving. However, I started from scratch and just copied the text over and everything seems to be working fine now. Thanks to everyone to helped me get this working
Last edited by vanillagorilla; 06-19-2019 at 10:55 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks