Sheet name not maintain in conditional formatting formula during sheet copy

[jaM2]

Win 10 Pro Version 21H2 OS build 19044.1826 Experience Pack 120.2212.4180.0
LO: 7.3.5.2

Objective: reference a common sheet with configuration data(less than 100 rows or columns).
Symptoms: Reference the attached TEST spreadsheet.
A.) working sheet(‘FIRST’) in cell $B$1, references via a VLOOKUP to the common sheet(‘Config’).
B) working sheet(‘FIRST’) also contains a conditional formatting $B$1 using a formula with the same
VLOOKUP syntax as $B$1 cell that always tests TRUE to switch formatting to the 'GOOD" style.
C) rt clicking the ‘FIRST’ sheet tab and selecting copy to create sheets ‘BeforeFirst’ and ‘AfterFirst’,
obviously one before and one after sheet ‘FIRST’.
D) Both ‘BeforeFirst’ and ‘AfterFirst’ cell $B$1 VLOOKUP array reference still contained common
sheet ‘Config’.
E) Conditional formatting formula VLOOKUP in sheet ‘BeforeFirst’ was altered to ‘FIRST’.
F) Conditional formatting formula VLOOKUP in sheet ‘AfterFirst’ was altered to ‘AfterFirst’.

P.S. Conditional formatting wizard is dropping the terminating sheet reference on the array spec.
Had to correct this for formatting to work.

Question: Is this a bug or a ‘feature’ of conditional formatting, and if so is there a syntax form to force the common sheet references to not be altered?
2022-07-27T04:00:00Z
testCFvlookup.ods (8.8 KB)

[joshua4]

After copying the sheet, try doing a hard recalculate (ctrl+shift+F9) before looking at the conditional format. I think it might heal itself. Or not.
I haven’t figured out the pattern yet. Hard recalculate (ctrl+shift+F9) seems to be needed to get the conditional formating to light up at times. Is it referencing the same relative sheet to the new sheet as was the relative reference from the copied sheet to its referenced sheet? So if the original reference was one-to-the-right does it then reference one-to-the-right from the copied sheet?

[jaM2]

1. Hard calculate no effect…
2. intent is a simple back reference to the ‘Config’ sheet by all subsequent copied sheets. All sheets will contain the back reference.

[EarnestAl]

Maybe name the range $Config.$A$1:$Config.$B$3 and use the named range in the condition? That way you get an absolute reference

[jaM2]

That did the trick…thank you…I couldn’t figure out why my prior testing of named ranges didn’t work until I discovered you have to ‘clear direct formatting’ to edit existing formula.
Thanks again for your effort.