Search code examples
exceldynamicchartsformulaoffset

Excel Dynamic Chart using named range that can be empty

I have chart in excel that is driven entirely by dynamic named ranges (using OFFSET/COUNT formulas). These named ranges will under certain conditions be of zero length.

As the charts are trying to generate based on what is essentially an empty named range excel is throwing the following error:

"A formula in this worksheet contains one or more invalid references"

I know that it is the named ranges causing the error and I dont want to remove them, but I'm having a hard time thinking of a way around it.

Does anyone have a workaround or fix for this.

The formula that is causing the problem is:

=OFFSET($A$2,0,1,COUNTIF($A:$A,">=TODAY()"))
Solution

  • You could adjust the formula so it returns at least one cell. This just makes the offset reference a height of at least 1.

    =OFFSET($A$2,0,1,max(1,COUNTIF($A:$A,">=TODAY()")))

    Alternatively you could wrap the formula in an iferror statement. The na is because this is effectively ignored by most charts.

    =iferror(OFFSET($A$2,0,1,COUNTIF($A:$A,">=TODAY()")),na())