Excel 2010 Axis Labels From A Single Cell 1

[PWD]

Good afternoon, is it possible to set up a single cell where we can make the changes to the axis label range and then refer to that within each of several charts?

For example it's tedious to have to increment each one from

=Totals!$AD$6:$BA$6

to

=Totals!$AE$6:$BB$6

when we have a new month. I'd like to be able to just change it once in cell B1.

I've been looking and have tried creating a Named Range in B1 "Months" =OFFSET(Totals!$B$1,0,0) and then putting that in the Axis labels but all I get is just one labels of "Totals!$AD$6:$BA$6"

Many thanks,
D€$

 

[combo]

I haven't tested in 2010, eaelier excel versions accepted worksheet level names (scope was important, workbook level names did not work). Try to add worksheet level name "Months" with =OFFSET(Totals!$B$1,0,0) as refers to formula.

combo

 

[SkipVought]

Use a Structured Table as source for your chart. As your table expands/contracts, your chart will track to the table rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi guys, I've created a Worksheet-level name of "Months" with =OFFSET(Totals!$B$1,0,0) as refers to formula. So far so good. I've tried putting =Totals!Months for the Axis label range: but all I get in the Axis Labels is just the literal "Totals!$AD$6:$BA$6" in the first segment.

Perhaps I should start at the beginning. The purpose of this exercise is to assist in each new month's charting where we want to show only 24 months worth of data at a time and instead of having to go into each chart and amend "$AD" to "$AE" and "$BA" to "$BB" (etc. for each new reporting month), we will have just one cell, B1, where we amend these criteria - currently "Totals!$AD$6:$BA$6"

Does this clarify things and/or point to where I'm going wrong?

Any other suggestions for what I'm trying to achieve are most welcome too!

Many thanks,
D€$

 

[SkipVought]

You can use a filter on the table, as the chart only displays visible data. You could also use OFFSET by varying the second or third argument to position the start of your 24 month range.

A live workbook sample might be helpful.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Sorry, I didn't understand your intention.

In your attempt you define Months as single fixed cell, B1. Referring to it you get its contents. Instead, you need a range of values. You have two possibilities:
1) if you put address in B1, Month name should refer to =INDIRECT(Totals!$B$1). This assigns range with address written in B1,
2) alternatively you can pass parameters to OFFSET, in this case for instance you can declare Month as =OFFSET(Totals!($AD$6,0,Totals!$B$1,1,24). In B1 you change shift related to AD:BA, by writing 0, 1, 2,...

Both solutions do the same: return an array that can be moved, either by changing address text or by shifting named range using one of arguments in OFFSET.

combo

 

[PWD]

Hi Skip, I've attached the relevant worksheet from our workbook.

Many thanks,
D€$

 

[SkipVought]

 

[SkipVought]

Check out the formula in AA1
[tt]
=OFFSET($AA$6,MATCH([highlight #FCE94F]AE1[/highlight],$B$6:$B$12,0)-1,COUNTA(AA7:IV7)-23,1,24)
[/tt]
[highlight #FCE94F]AE1[/highlight] contains a Data Validation dropdown to select Dates or one of the 6 chart data sources. This is just FYI.

The assumption is that when you add another value in Admissions (row 7) the COUNTA will cause the column pointer (arg 3) to the right to display the last 24 columns of data.

To use in both the chart data for the x and y axes, you can enter the hard value for each of the 6 charts in place of [highlight #FCE94F]AE1[/highlight].


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I've made an interactive chart where any of the 6 can be selected.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Wow, thanks Skip; I'll see how much of this I can incorporate!

Many thanks,
D€$

 

[combo]

I've implemented OFFSET formula in local name for selecting months shift and row data.

PS>: you need to link again spin button to Totals!B3, I used named range, this worked, but was not saved.

combo

 

[PWD]

Hi guys, this is all fabulous stuff but I fear we may have digressed, slightly. My people are really conservative and like what the have currently. What I was trying to do for them was just to remove the drudge of having to go in to each chart and amend the Source Data every month, e.g. from Totals!$AC$6:$AZ$6, Totals!$AC$8:$AZ$8 to Totals!$AD$6:$BA$6,Totals!$AD$8:$BA$8, and thus also eliminating the potential for human error. So all I wanted to have was to have either named ranges or just certain referenced cells for the Months and each chart's relevant data ranges.

Do you think this is possible or should I just let them carry on merrily as they have always done?

Many thanks,
D€$

 

[PWD]

Hmm, Jon Peltier says here:

http://superuser.com/questions/464039/creating-a-chart-using-a-dynamic-named-range-in-excel-2010

1.When INDIRECT is used to define ranges, charts often don't use the ranges correctly, and often don't even accept these named ranges. There are better (more robust) ways to define dynamic ranges, using for example INDEX or OFFSET.


2.Even if the name would be recognized by the chart, it will be converted to a cell address in the Chart Data Range box of the Select Data Source dialog. Names only persist in the ranges for X, Y, and series name for each individual series.


3.If you prefix the range with the sheet name in cell D1? Charts need fully qualified ranges, so if cell D1 contains Sheet1!$A$1:$B$5 you can use your defined name in the Chart Data Range box of the Select Data Source dialog. Note that, per point 2, Excel will convert this range to its cell address when you click OK.

Dang, that's what I found when I just named the month range and put that in the Source Data. Oh well.

Many thanks,
D€$

 

[combo]

If you look at the chart input in my file you can see that it uses OFFSET in sheet level names and named ranges in Totals worksheet to set dates window width and shift, and data row. No macros.
You can easily adapt this to your initial charts. The only difference is that instead of address as text one of OFFSET argument is number. This can be linked with forms combo box (input dates in table, selection to OFFSET), so user can only select beginning of period.

combo