how 2 get around the "absolute references only" draconian rule with conditional formats?

[juntjoo]

So I was thinking, since Excel won't let you use relative references in conditional formats, mean, there has to be a cheat to allow you to paste a format's absolute references to other cells but changing the cells to their RELATIVE, respective cells. C'mon, I know one of you knows. PM me. Your secrets are safe with me.

[BadlySpelledBuoy]

I'm not 100% what the ask is here. If I use a formula such as =$A$1="X" in conditional formatting for cell B1 then copy that to cell D1 it will still point at A1. But if I use =A1="X" (same formula but without the $s) in B1 and then copy that to D1 it will now point to C1.
So you can use both absolute and relative references in CF.


BSB

[juntjoo]

Is that with the latest version of Excel? I've got 2010 and it's always been a rule, I think at least up to 2013 no? How much is 365? is there a free version?

[BadlySpelledBuoy]

Have you tried it?

I cannot test it as I don't have access to anything earlier than 2019 right now but I'm pretty sure it's always been like that...

*off to search down the back of the internet for further info*

BSB

[juntjoo]

Yeah. I guess I'm "old school". I'll look into 365

[BadlySpelledBuoy]

Give it a go before you upgrade (I'm sure I used to do this back when I used Excel 2003!). Then upgrade anyway, cos you know, it makes sense

BSB

[juntjoo]

it's subscription based. No way. I ain't gonna be slave to any corporation unless they offer stock options with dividends that exceed the subscription cost. Hmm...