INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel 2010 Axis Labels From A Single Cell

Excel 2010 Axis Labels From A Single Cell

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

Quote:


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

to

Quote:


=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€$

RE: Excel 2010 Axis Labels From A Single Cell

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

RE: Excel 2010 Axis Labels From A Single Cell

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Axis Labels From A Single Cell

(OP)
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€$

RE: Excel 2010 Axis Labels From A Single Cell

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Axis Labels From A Single Cell

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

RE: Excel 2010 Axis Labels From A Single Cell

Check out the formula in AA1

=OFFSET($AA$6,MATCH(AE1,$B$6:$B$12,0)-1,COUNTA(AA7:IV7)-23,1,24)

AE1 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 AE1.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Axis Labels From A Single Cell

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

Many thanks,
D€$

RE: Excel 2010 Axis Labels From A Single Cell

(OP)
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? dazed

Many thanks,
D€$

RE: Excel 2010 Axis Labels From A Single Cell

(OP)
Hmm, Jon Peltier says here:

http://superuser.com/questions/464039/creating-a-c...


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. sad

Many thanks,
D€$

RE: Excel 2010 Axis Labels From A Single Cell

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close