I have been learning about Names and have learned how to create a Named Range to dynamically update row numbers in this manner.
Name=Sales
This will automatically update the size of the range as new rows are added.
Next I learned how to use this Name to define a chart series by using this as the series values.
This all works great until I try to use this technique when building a template.
(Note the different file extension)Then I get the "Reference is not valid. Reference must be to an open worksheet" error. What am I doing wrong?
Name=Sales
Code:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
This will automatically update the size of the range as new rows are added.
Next I learned how to use this Name to define a chart series by using this as the series values.
Code:
='dynamic chart.xlsx'!Sales
This all works great until I try to use this technique when building a template.
Code:
='dynamic chart.xltx'!Sales