CODEWITHSUNDEEP
vbaexcelchartsrange
Nknautz
Nknautz

Reputation: 5

Dynamic Chart Range - a formula in this worksheet contains one or more invalid references- VBA

I am using named ranges to create charts dynamically. I take a snapshot every 5 minutes of data and add a new line. The charts update correctly when new data is added.

However, when I clear the first line of data then I get error messages saying

a formula in this worksheet contains one or more invalid references.

I can't do anything because the pop up won't stop coming up. Is there a way to make my formula stronger so it accounts for when the first row is cleared?

My formula:

=OFFSET(Spreads!$B$130,1,0,COUNTA(Spreads!$B$131:$B$1048576),1)

The header starts in cell B130, but the first line of data is in B131. I tried using the Indirect formula, but that didn't seem to help. I am not deleting rows either, I am just clearing the data.

Upvotes: 0

Views: 626

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

Try using INDEX:

=INDEX($B:$B,131):INDEX($B:$B,COUNTA($B$130:$B$1048576)+130)

This returns a reference to the 131st row in column B extended to the 130th+COUNT row in column B. It's also non-volatile unlike OFFSET - They are only recalculated when any of the function's arguments change.

Upvotes: 2

Related Questions