Can i get a table range in a conditional format to expand with table expansion?

[TomCon]

I have a defined name, "todayrow" that, on most days, moves forward by one row. I am trying to reference the "todayrow" row within a table.

When i create a formula on the sheet, it looks like this.
=INDEX(rng_data[Al7dA],TodayRow,1)<=5 (formula 1)

When i create the same formula in a conditional format (using "use a formula to determine which cells to format"), it will not reference the table, and so it deconstructs to an A1C1 type reference, and looks like this.
=INDEX(data!$X$2:$X$1280,TodayRow,1)<=5

But the problem with the conditional formula reference is that it does not update as rows are added to the table. Eventually it becomes outdated and evaluates to #REF when "todayrow" gets higher than the number of rows in the table. But in reality, the table itself has expanded so that todayrow is validly within the range of the table. Is there a workaround, within conditional formats, for this?

Now, i have thought of a workaround, simply to put the formula (formula 1) somewhere in a sheet cell, and then reference the conditional format to the value of that cell.

But, what i am looking for is "a more natural/clean" workaround, to put a formula in the conditional format formula itself that will suffice and not cause me to have to set a part of a worksheet aside just for this purpose. That way the entire conditional format condition is within the conditional format definition (as it usually is) and does not require visibility on a sheet cell.

Any thoughts? Thanks!

 

[Micron]

I don't know much about Excel tables; I'm really more of an Access guy. However, I do know that you can create a dynamic named range. Perhaps if you did that, your table could reference it and be correct when any range rows are added or removed?

I once based an Excel chart on a dynamic range (and pushed the data from Access to Excel). In the process, I deleted the range data, then pushed it into Excel. The chart always reflected the new data set. It seems logical to me that a table could work the same way as the chart did.

 

[TomCon]

Thank you, yes, i was able to make what you suggested above, work. It still seems very much like a "hack/workaround". I'll give the details of what i did.

I have a table in the workbook named rng_data.
So, i defined a name like this.
rng_data_shadow = rng_data
(seems kind of silly to have to do this, doesn't it?)

Now, my conditional format definition can look like this.
=INDEX(rng_data_shadow,TodayRow,COLUMN($X$1))<=5
And yes, the rng_data_shadow does expand whenever the table expands, and therefore the INDEX function continues to function as the table expands.

Note: cannot use what Excel generates if you create this formula on the sheet in a cell, which is more clear to read and understand and maintain in the future of course, which would be:
=INDEX(rng_data_shadow,TodayRow,COLUMN(rng_data[[#Headers],[Al7dA]]))

Thanks for the suggestion.

Just to editorialize, i have heard folks say that the "table" feature in Excel was only "half-finished" and now i see particularly what one would mean saying that. The fact that one would need to define a shadow range to use the feature in a conditional format...seems more like engineering laziness than anything else. Certainly cannot see why you would go an actual effort to prevent users from using table references in conditional formats, just didn't feel like investing in it, i guess.

 

[Micron]

This is what I had in mind for a dynamic named range (IIRC it is based on count of rows in BB).
Named Range is "DailyCountRange"
=OFFSET(DATA!$B$6,0,0,COUNTA(DATA!$B:$B),1)
I've no idea if that's workable with a table, but it should be if the table is based on the named range. So if your cf could be based on the named range you wouldn't have to refer to the table at all?

Since you have it working, perhaps there's no point in trying it, but if you did and it worked it may make things easier for you in the future.
Thanks for the recognition.

 

[Fluff]

You could use indirect

=INDEX(indirect("rng_data[Al7dA]"),TodayRow,1)<=5

 

[TomCon]

You could use indirect

=INDEX(indirect("rng_data[Al7dA]"),TodayRow,1)<=5

Actually, wow...i really like this idea the best! Generally, I do not like to use INDIRECT as it is so vulnerable as to if you insert rows, columns, or cells. So, i don't even usually think about the possibility. But as long as you combine INDIRECT with the structured reference, i think it should still be resilient. And i do see that protecting the Conditional Format machinery from the structured reference through use of INDIRECT does work. I wish i could mark multiple solutions as good. It could be an upgrade to the board to allow that!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Micron]

You can swap the accepted answer to one you like better. These things are kinda like a feather in your cap so nice to get the appreciation, but it's not like it's a dollar in your wallet.