• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel axis query (dynamic oppose to static)

robjowens

New Member
hi there,


I have a range of data (E6:BQ10 or 'year 1 to year 65') that needs to be charted.


http://img40.imagehaven.net/img.php?...5FAU_excel.GIF


there is an 'override' input that allows the user to curtail/wind-up the development before the 65 years. I need this value to be the upper limit on the x axis so that I avoid the blank space


http://img40.imagehaven.net/img.php?...Y35_excel2.JPG


Thanks,

rob
 
Rob

Your links aren't working


but If the X Axis goes from E6:BQ6

setup a named range called XAxis and put the following formula in

=OFFSET(Sheet1!$E$2,0,0,1,COUNTA(Sheet1!$E$6:$BQ$6))

Then on the Chart select the Data Range 1, which will be like

=SERIES(,Sheet1$E$6:$BQ$6,Sheet1!$E$7:$BQ$7,1) <- Note the 1 on one end

which is used to define the X-Axis


and change it to be like

=SERIES(,Book1!XAxis,Sheet1!$E$7:$BQ$7,1)

Changing the Book1 reference to the name of your workbook


You could also make the Data Ranges Dynamic by adding more named ranges

Data1 = Offset(XAxis,1,0)

and change the series in the chart to be like

=SERIES(,Book1!XAxis,Book1!Data1,1)

etc


Then have a browse through

http://chandoo.org/wp/tag/dynamic-charts/
 
Thanks Hui,


you've got me pointed in the right direction but I've had a go this morning and can't apply it to my spreadsheet.


Those links again:

http://img40.imagehaven.net/img.php?id=JTA8YR5FAU_excel.GIF

http://img40.imagehaven.net/img.php?id=OB55F8JY35_excel2.JPG


Any further help would be greatly received.

rob
 
Rob

Because you have used a Column Chart (stacked in this case) you will need to set a Dynamic X-Axis and Individual Named Range for each of the 4 Ranges in your chart

So the Series formula for each Chart will end up looking like


=SERIES(,Book1!XAxis,Book1!Data1,1)

=SERIES(,Book1!XAxis,Book1!Data2,2) etc
 
Apologies, I'm really struggling to understand your previous message, i'm not excel chart savvy at all - the finance model guy retired last week and i've been thrown into designing a dashboard by Friday!


http://chandoo.org/wp/2009/11/12/topx-chart/

This is the kinda of chart I'm looking for - i guess the rank would be based on the year in this instance?


thanks,

Rob
 
Rob

You will need to define 7 Ranges

1 for the X-Axis and

6 more, 1 for each data Range you want in your chart

The XAxis named range will be be

=OFFSET(Sheet1!$E$6,0,0,1,COUNTA(Sheet1!$E$6:$BQ$6))

The Named Data Ranges will be


for Data Range 1

Data1 = Offset(XAxis,1,0)

where the value 1 will be changed to represent the number of rows the Data Range is below the X-Axis Range


Repeat this for the 6 Ranges of Data you are graphing


Once you have your named Ranges Set-up, whilst still in the Named Ranges Dialogue, select each named ranges and click in the equation box, That will show you that it is selecting the correct data, Adjust the formula's if it isn't.


Now go back to your chart and in sequence select each Range by selecting the appropriate bar

The equation box will show something like

=SERIES(,Sheet1$E$6:$BQ$6,Sheet1!$E$7:$BQ$7,1)

There may be a Range or Text in front of the First Comma


In the equation Box you need to edit the formula to be like

=SERIES(,Book1!XAxis,Book1!Data1,1)

Where XAxis and Data1 are the named ranges you defined earlier


Adjust the Book1 name to be whatever your Spreadsheet is called

Leave whatever is in front of the first ,


Do the same to all the bars

The Chart won't change until you have done them all as the longest range will define the X-Axis length
 
Sorry Hui, spent the past 3 hours labouring over this with no success.


1.) Defining a range; do you mean insert->name->define then copy the formula above in the 'refers to:' field? (i.e, the relevant range doesn't need to be highlighted)


2.) 'equation box' = fields name, value and category axis label on the series tab.


Once again, sorry this is so painful!

rob
 
What Hui suggests is using OFFSET to create the input ranges for your charts. Here is a tutorial, You can understand the concept thru this http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series
 
Back
Top