Michal Rosa
New Member
- Joined
- Jul 20, 2011
- Messages
- 1
Hi all,
I'm trying to create a chart sourcing its data from a dynamic, named range (Excel 2007).
Defining the range seems pretty easy, variable "months" is defined as =OFFSET(INDIRECT(ADDRESS(MATCH(ReportMonth,'Sheet 1'!$A:$A,0)-11,1,,,"Sheet 1")),0,0,12,1) - it selects a dynamic range of 12 months starting from date as defined by another variable "ReportMonth". So far so good and on its own it works exactly as I want it to work.
However if I try to use it in a chart, for example =SERIES(,'File name.xlsx'!months,'File name'!values,1) I get an error message "A formula in this worksheet contains one or more invalid reference" which is annoying as hell since on their own "months" and "values" work fine.
Hardcoding "months" as for example =OFFSET(INDIRECT("'Sheet 1'!$A$24"),0,0,12,1) works fine so I'm not sure what exactly is wrong with my range that seems to work fine in a formula but not when I try to use it in a chart.
Any help would be much appreciated.
Cheers,
Michal
I'm trying to create a chart sourcing its data from a dynamic, named range (Excel 2007).
Defining the range seems pretty easy, variable "months" is defined as =OFFSET(INDIRECT(ADDRESS(MATCH(ReportMonth,'Sheet 1'!$A:$A,0)-11,1,,,"Sheet 1")),0,0,12,1) - it selects a dynamic range of 12 months starting from date as defined by another variable "ReportMonth". So far so good and on its own it works exactly as I want it to work.
However if I try to use it in a chart, for example =SERIES(,'File name.xlsx'!months,'File name'!values,1) I get an error message "A formula in this worksheet contains one or more invalid reference" which is annoying as hell since on their own "months" and "values" work fine.
Hardcoding "months" as for example =OFFSET(INDIRECT("'Sheet 1'!$A$24"),0,0,12,1) works fine so I'm not sure what exactly is wrong with my range that seems to work fine in a formula but not when I try to use it in a chart.
Any help would be much appreciated.
Cheers,
Michal
