From what I can make of the data, the problem can be solved by creating a named range that refers to:
=OFFSET($C$2,0,0,1, COLUMNS(myTable))
The change to the formula is:
=IF(MAX(myRange)>0,COUNTIF(myRange,">1")/COUNT(myRANGE),0)
- Where
myTABLE is the name of your table and
- Where
myRANGE is the name of the new named range
How it works:
The named range counts the number of columns in the table and updates whenever one is added it removed. Offset anchors to the left side of the table and extends to the right by the number of columns in the table. The formula drops the cell reference in favor of the named range for improved legibility. It can be copied dragged pasted and moved all over the place and still point to the correct range. With adequate scope, you could put it anywhere in the workbook without issue.
Note: My biggest pet peeve about this method comes when copying the sheet to a a new workbook. Named ranges are so awesome that a workbook scoped range will point to the original workbook even after it has been copied to a new workbook. Sometimes that's a good thing, but us mostly it's a nuisance.
I believe an alternative answer will compliment the first answer because there are innumerable occasions where one may want a dynamic range and simultaneously not want a table.
And that's the clue, for more information you can search for dynamic range. Or more specifically: dynamic named range.
To answer your question:
Yes, it is possible and it's also very handy. For example, the print area can expand and contract with your data and it's very easy to interact with because you work with by name rather than address. And when using VBA, I find it's generally easier to work there named range than a table, that is, unless the table is bound to a variable, in that case they would be equal in my eye. There is just something a fundamentally loathe about typing listobject("table name").listcolum("table colum name").datapropertyrange I almost have a visceral reaction when I have to type it.
The gist of it creating a dynamic named range is simple. Open the name editor, name a range, and use offset() and counta() to define your range. Counting non-empty cells pulls double duty. it is the trigger to recalculate the range and it provides an index that can be used with offset to change the range area.
Here is one example: =OFFSET($A$1,0,0,COUNTA($A:$A),1)
It is very straight forward. With one exception, print range.
For whatever reason, print range doesn't behave the same way and doesn't update like a normal named range. To make it dynamic you create a named range like you normally would. This will be your print range so make it count. Then make your print range refer to that named range. That's all there is to it. The first named range points to the area you want to print and the print at range points to that named range.
