1

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()"))
Improve this question

2 Answers 2

Reset to default
0

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())
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Perfect. I went with your first option and it seems to have fixed the error without any side-effects. Much appreciated.
0

For the second option above: 

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

I had to set the iferror value to a range that contained NA()

=iferror(OFFSET($A$2,0,1,COUNTIF($A:$A,">=TODAY()")),$A$1)  Where A1 = NA()
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.