?
Solved

Excel 2010 graph with dynamic range columns

Posted on 2012-03-18
3
Medium Priority
?
585 Views
Last Modified: 2012-06-07
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:
="'Stat Table'!" & SUBSTITUTE(TEXT(ADDRESS(1,MATCH($Q$1,$4:$4,0),4),""),"1","") & ROW(D6)

Open in new window

(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)

Open in new window

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 example


Please see my problem attached
test1.xlsx
0
Comment
Question by:cookiejest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 50
ID: 37735722
Hello,

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('Stat Table'!$5:$5,MATCH('Stat Table'!$Q$1,'Stat Table'!$4:$4,0)+'Stat Table'!$Q$2-1)
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
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37735738
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
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 37735777
I guess what's simpler comes down to preference.

I always use the long, complex formula to define the range for the X axis, then continue with Offset() to simplify the identification of the ranges for the data series.
Yes, I know that Offset() is volatile, but in a situation like this one, it's absolutely not noticeable and not worth getting my knickers in a twist, so I put readability and less typing over volatility avoidance.

Then again, with many chart series, the repeated use of the same Match() could be avoided altogether by using another named formula for the Match() portion, e.g.

firstWeek =MATCH('Stat Table'!$Q$1,'Stat Table'!$G$4:$X$4,0)
lastWeek =firstWeek+'Stat Table'!$Q$2-1

Then, if you prefer to spell out each series formula with a full fledged index, you can use something along the lines of

=INDEX('Stat Table'!$G$4:$X$4,firstWeek):INDEX('Stat Table'!$G$4:$X$4,lastWeek)

Again, this is not for speed, necessarily, but more for readability.

cheers, teylyn
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question