Whilst attempting to wrestle some data into submission and line ’em up like good little datum on an Excel chart, I ran into an issue attempting to utilise a dynamically defined range of data as the chart’s data source.

I had followed the sages’ advice found over the Internet, first finding out you cannot use an INDIRECT() function directly while specifying chart series source data, then attempting to replicate the advice on using named ranges (as an additional level of indirection).

Unfortunately, try as I might, I still kept getting the dreaded “Excel found  a problem with one or more formula references in this worksheet” error, for which the description and suggestion was to “Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.”, whenever I tried to save or even tab away/take focus away from the data input field:

If you are sharp-eyed and am an Excel expert, laugh at my folly and move on… If you are still clueless on what the cause is, please do continue reading…

Attempts at some “lower-level” “unit testing” did not seem to help – =INDIRECT("Sheet1!$A$2:Sheet1!$A$"&COUNT(Sheet1!$B:$B)) simply threw a #REF! error…

I finally stumbled onto the root cause: I had specified the worksheet name also in the “to” range specification used as part of the range specification in the named range.

I need to remove the worksheet name in the “to” range: i.e. =INDIRECT("Sheet1!$A$2:Sheet1!$A$"&COUNT(Sheet1!$B:$B)) to become =INDIRECT("Sheet1!$A$2:$A$"&COUNT(Sheet1!$B:$B)).

Once I did that, all was right with the (Excel) world again (for now)…