Is it possible to use IF function inside the Conditional Formatting formula?

Sh8dyDan

New Member
Joined
Dec 20, 2023
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am working to create a 2-week timesheet for personal use to track hours and calculate pay to later compare to the actual pay stub. We have a non-rotating day shift and a rotating 12-hour shift. We have a list of observed holidays I'd like the row that a holiday falls on to have a colored background. The kicker is that non-rotating and rotating shift holidays can at times be observed on different days. I created two defined name lists for each shift with the dates of their holidays, HolidayNonRotation and HolidayRotating. I'd like to use a set of Radio Buttons "Non-Rotating Shift" and "Rotating Shift" to switch between formatting. I can not find a solution similar to my use or create one of my own that works.

I've tried the code below with no luck. When the formula is TRUE it always returns FALSE. Even when I change the state of the Radio Button. I have verified referenced cells are correct.

Excel Formula:
=IF(AB6=1,MATCH($Z6,HolidaysNonRotating,0),MATCH($Z6,HolidaysRotating,0)

TimesheetScreenshot01.jpg
TimesheetScreenshot02.jpg


Thank you in advance,
Dan N.
 
Check this and revert - I think formula needs slight modification

Excel Formula:
=OR(And($AB$6=1,MATCH($Z6,HolidaysNonRotating,0)),And($AB$6<>1,MATCH($Z6,HolidaysRotating,0)))
I don't understand why it never changes states with a radio button click.

No way to attach a file?
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
I don't understand why it never changes states with a radio button click.
You can do what I did - The formula is 2 parts

Paste 2 parts in 2 different cells and test if it is giving True or False when Radio Button is click

Excel Formula:
=And($AB$6=1,MATCH($Z6,HolidaysNonRotating,0))
Excel Formula:
=And($AB$6<>1,MATCH($Z6,HolidaysRotating,0))

Then we shall think of some alternative, it that does not work

Test both separately to identify the problem area...
 
Upvote 0
Solution
You can do what I did - The formula is 2 parts

Paste 2 parts in 2 different cells and test if it is giving True or False when Radio Button is click

Excel Formula:
=And($AB$6=1,MATCH($Z6,HolidaysNonRotating,0))
Excel Formula:
=And($AB$6<>1,MATCH($Z6,HolidaysRotating,0))

Then we shall think of some alternative, it that does not work

Test both separately to identify the problem area...
Ok, so yeah, individually they work. Highlight changes to on and off with the radio button change.
 
Upvote 0

Forum statistics

Threads
1,223,979
Messages
6,175,760
Members
452,668
Latest member
mrider123

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