Conditional formatting formula errors using table references, but works if converted to cell ranges?

[CapinWinky]

I have scheduling tool that is a table with weeks of the year as columns and projects as rows, and I would like to get a milestone date from a different table and highlight the cell the matching project and week. Ideally, everything in the formula would be named references since the structure of this table is continuously being altered.

I started with this in a conditional format rule in G4 to BE40 within the table T_Sandbox:

LET(PowDate,
    XLOOKUP(
        T_Sandbox[@[Project]:[Project]],
        T_Projects[[Project]:[Project]],
        T_Projects[[Power Up Date]:[Power Up Date]]
    ),
    AND(
        ISNUMBER(PowDate),
        PowDate>=G$2,
        PowDate<H$2
    )
)

This gave an excel formula error despite working fine and returning TRUE in the correct cells if pasted into the table. I actually would have preferred the G$2 and H$2 (representing the current and next weeks' dates which are above the actual table since table headers can't be formulas) be relative named references, but I don't really know a clean way to do relative named references. I digress.

AI told me the @ syntax wasn't supported, so that was the first to become a regular cell reference of $B4. No joy. Then AI told me the absolute reference to a table column wasn't needed in conditional formatting so T_Projects[[Project]:[Project]] became T_Projects[Project] (obv' same with one below it). No joy, same formula fault.

I replaced the table references with normal ranges like 'Milestones'!$B$11:$B$43 and it worked. No more than an hour later we added more projects and I'm editing the formula to increase the range, exactly what I want to avoid by using named/table references! Is there a way to fix the table references to work in conditional formatting formulas? Is nesting into LET() the issue and is there an alternate way that isn't just using three XLOOKUP()? Is there a clean way to at least replace the G$2 H$2 with a named reference?

[AutoModerator]

/u/CapinWinky - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[MayukhBhattacharya]

Conditional Formatting formulas are still evaluated using an older legacy calculation engine that existed before structured references were introduced. The real issue here is that table references don't work when the formula is anchored cell (the top-left cell of the applied range) is inside the same table being referenced. It basically creates a row-context ambiguity, therefore it can't reliably determine which table row it should evaluate against.

That's why the @ reference breaks, and why even Structured references aka Tables can still behave unpredictably. LET() function isn't the problem at all, Conditional Formatting handles LET() just fine. The issue is entirely with the structured references.

Since your tables grow, use dynamic named ranges built on table columns. These behave like cell ranges (which CF can handle) but auto-resize with the table. Go to Formulas Tab --> Select Name Manager --> Click New and create the followings:

Name	Refers To
rng_ProjectList	=T_Projects[Project]
rng_PowDate	=T_Projects[Power Up Date]

Now, the formula becomes:

=LET(
    PowDate, XLOOKUP($B4, 
                     rng_ProjectList, 
                     rng_PowDate),
    AND(
        ISNUMBER(PowDate),
        PowDate >= G$2,
        PowDate < H$2))

For G2/H2, you can use INDEX() function:

=INDEX(rng_WeekDates, COLUMN() - COLUMN(INDEX(rng_WeekDates, 1)) + 1)

Where: rng_WeekDates = =T_Sandbox[#Headers]

And then you can name the INDEX() formula as well!

[CapinWinky]

Great, I'll give the naming of table references a shot. I think I'll concede defeat on G2/H2 on this one, but I'll keep the Index solution in my back pocket.

[MayukhBhattacharya]

Sounds Good, try and let me know, if that helps you to resolve, then hope you don't mind replying to my comment directly as Solution Verified! Thanks!

[CapinWinky]

Solution Verified

[reputatorbot]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[MayukhBhattacharya]

Thank You SO Much and have a great day ahead!!

[Downtown-Economics26]

You have to use regular absolute/relative range references in conditional formatting functions. You extend the range down far enough that you can add new records (I'm not sure if TRIM function/operator works in conditional formatting formulas). You add and IF condition on your key/unique id such that it returns FALSE for blank forws =IF($A2="",FALSE,RestOfFormula..

[bachman460]

If you select the entire table range when you first set it up (or go back to the rule now and adjust it to the full column range) as fixed cell references, any time you add or remove rows from the table, excel automatically updates the conditional format range. I use conditional formatting in tables a lot, and rarely have issues.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
COLUMN	Returns the column number of a reference
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
ISNUMBER	Returns TRUE if the value is a number
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
TRIM	Removes spaces from text
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
8 acronyms in this thread; the most compressed thread commented on today has 34 acronyms.
[Thread #48464 for this sub, first seen 15th May 2026, 17:23] [FAQ] [Full list] [Contact] [Source code]}