cookiejest
asked on
Excel 2010 graph with dynamic range columns
Hi There,
I am trying to use the offset function with an indirect lookup for the first variable. (the aim being to populate a graph with a dynamic range calculated using a chosen start date and number of periods).
I have a formula that looks up the value of what needs to be in the reference variable for the OFFSET function:
I then reference it in the offset like this:
Please see my problem attached
test1.xlsx
I am trying to use the offset function with an indirect lookup for the first variable. (the aim being to populate a graph with a dynamic range calculated using a chosen start date and number of periods).
I have a formula that looks up the value of what needs to be in the reference variable for the OFFSET function:
="'Stat Table'!" & SUBSTITUTE(TEXT(ADDRESS(1,MATCH($Q$1,$4:$4,0),4),""),"1","") & ROW(D6)
(so this just looks up the Letter part that is unique to the chosen date and then the number of the column the offset is for).I then reference it in the offset like this:
=OFFSET(INDIRECT(C5),0,0,,'Stat Table'!$Q$2)
But I get an error message of #VALUE! , any ideas? The offset value works if I just point it directly at the cell like this: =OFFSET(H3,0,0,,'Stat Table'!$Q$2) for examplePlease see my problem attached
test1.xlsx
Similar approach to teylyn's, which dispenses with OFFSET as well as INDIRECT.
1) Created a sheet-scoped Name, ChartValues, with formula:
=INDEX('Stat Table'!$G$8:$X$8,1,MATCH(' Stat Table'!$Q$1,'Stat Table'!$G$4:$X$4,0)):INDEX ('Stat Table'!$G$8:$X$8,1,MATCH(' Stat Table'!$Q$1,'Stat Table'!$G$4:$X$4,0)+'Stat Table'!$Q$2-1)
2) Created another sheet-scoped Name, ChartLabels:
=INDEX('Stat Table'!$G$4:$X$4,1,MATCH(' Stat Table'!$Q$1,'Stat Table'!$G$4:$X$4,0)):INDEX ('Stat Table'!$G$4:$X$4,1,MATCH(' Stat Table'!$Q$1,'Stat Table'!$G$4:$X$4,0)+'Stat Table'!$Q$2-1)
3) Modified your chart to use the above Names as the source for both values and labels
4) Modified the x-axis to use a text label rather than a true date label
Q-27637787.xlsx
1) Created a sheet-scoped Name, ChartValues, with formula:
=INDEX('Stat Table'!$G$8:$X$8,1,MATCH('
2) Created another sheet-scoped Name, ChartLabels:
=INDEX('Stat Table'!$G$4:$X$4,1,MATCH('
3) Modified your chart to use the above Names as the source for both values and labels
4) Modified the x-axis to use a text label rather than a true date label
Q-27637787.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you don't need the helper cell with the cell address and you can avoid having to use Indirect() to build the range.
Set up two named ranges in the name manager:
chtXaxis =INDEX('Stat Table'!$5:$5,MATCH('Stat Table'!$Q$1,'Stat Table'!$4:$4,0)):INDEX('St
chtSeries1 =OFFSET(chtXaxis,3,0)
Then use the chtXaxis name for the X axis categories and the chtSeries1 name for the first series.
See attached.
cheers, teylyn
test1.xlsx