Conditional Formatting for an exact formula

[cappla011]

Hey all,

This probably a silly question but I'm struggling to get it to work. Basically I have a formula in a worksheet that multiple people use. Often times they adjust the formula by adding numbers to the end or something. The formula is something like this --

=SUM(SUMIFS('2024Data'!$R:$R,'2024Data'!$C:$C,"2024",'2024Data'!$D:$D,$G$21,'2024Data'!$G:$G,{"11*"}))/1000-AC24-AC25

Basically I want to do conditional formatting to make sure the formula stays the exact same and no one changes it. If someone adjusts the formula, I want the background to go red. What should I enter into the Rule formula to make this work? Thanks all!

 

[Joe4]

Wouldn't it be easier to simply lock the cells with formulas from being edited?

 

[cappla011]

Unfortunately people freak out when we lock the cells, so I was hoping to just use conditional formatting

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cappla011]

I updated my account -- we are using Microsoft 365

 

[Fluff]

Thanks for that, what cell is your formula in?

 

[cappla011]

In this example, AL24

 

[Fluff]

Ok, firstly you can change your formula to

=SUMIFS('2024Data'!$R:$R,'2024Data'!$C:$C,"2024",'2024Data'!$D:$D,$G$21,'2024Data'!$G:$G,"11*")/1000-AC24-AC25

There's no need for the SUM or the {}
Then you can use

=FORMULATEXT(AL24)<>"=SUMIFS('2024Data'!$R:$R,'2024Data'!$C:$C,""2024"",'2024Data'!$D:$D,$G$21,'2024Data'!$G:$G,""11*"")/1000-AC24-AC25"

But this will only work on that one cell.

 

[Joe4]

Unfortunately people freak out when we lock the cells, so I was hoping to just use conditional formatting

Seems a bit odd that would freak them out, especially if you don't want them changing it. Why would they "freak out"?
Note that Conditional Formatting won't stop them from changing it (or deleting the formula altogether!).

 

[cappla011]

Thank you so much! the =FORMULATEXT is exactly what I was looking for. That will help me with the other formulas. You rock!