Forum Discussion
Adding Two Ranges for a Single Bar in a Chart
Whilst a Series definition might not accept an array formula, it will accept a defined name (sheet local names are useful because the name must be fully qualified by its parent sheet or book, and users tend to create shorter names on sheet tabs than for file names). The Name then contains the formula.
Note1: The concept of a named range is only a special case of the named formula in which the name happens to resolve to a sheet reference.
Note2: In extreme cases, complex charts can be created with no numbers coming from worksheets.
nkapran Why save on space? You have over a million rows at your disposal. And if it's really important you can hide the rows you don't want to show.
Riny_van_EekelenSimply put, because I want to make things as programmatic as possible. Part of the problem I'm running into with the systems I have inherited is that EVERYTHING is hardcoded, especially in Excel. No named ranges, no dynamic formulas, etc. Part of why I want this to be formulaic is because A) we can't assume that the amount of data that we want in the chart right now is going to be constant, and B) I want to convert all of the data sources into named ranges, so the more I can have the calculations happening in formula, the fewer ranges I need to make and the fewer names I need to juggle. Why make a new named range for one specific calculation which might not even be the one I need in the future?
So, thanks for the suggestion, but I still need an answer to my specific question.