+ Reply to Thread
Results 1 to 6 of 6

Help with "Define Name" & auto-updating graphs

  1. #1
    Registered User
    Join Date
    02-28-2011
    Location
    Ann Arbor, Michigan
    MS-Off Ver
    Excel 2007
    Posts
    6

    Help with "Define Name" & auto-updating graphs

    Hello,

    I am having some difficulties setting up a large work book with auto-updating graphs.

    I used "Define Name" to set up named ranges for the graphs as such:
    Month: =OFFSET('4A'!$A$2,0,0,COUNTA('4A'!$A:$A)-1)
    Count: =OFFSET('4A'!$B$2,0,0,COUNTA('4A'!$A:$A)-1)

    This works fine to set up a graph for worksheet "4A", but the file I'm working with has 4A,4B,4C,5A,5B,... etc., etc. Is there a way I can make the names "Month" and "Count" always refer to the active sheet, and not have to define names for each sheet?

    See the attachment for a simplified version of what I'm working with.

    I greatly appreciate any help you can offer!
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,406

    Re: Help with "Define Name" & auto-updating graphs

    Hi,

    You can define a named range such as SheetName, that refers to

    =MID(GET.DOCUMENT(1),1+FIND("]",GET.DOCUMENT(1)),1256)

    In use in the worksheet will return the active sheet name via =SheetName

    and can be used to reference a range via the indirect function.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    02-28-2011
    Location
    Ann Arbor, Michigan
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help with "Define Name" & auto-updating graphs

    Thanks for the help. That formula you provided allowed me to use sheet name as a variable and get it into the Defined names where I needed using the indirect function.

    However, - now I can't get the graphs to use the named ranges. Would you mind taking a quick look at this version and letting me know how I can fix this? I wasn't having the graph problem in the earlier version. The named ranges work fine for other functions (=SUM(Count) on each sheet works fine), but not with the graphs. I have been staring at it just looking for errors for an hour now. I appreciate the help!
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Help with "Define Name" & auto-updating graphs

    What graphs? Your latest example does not include any.

    The cell references in the range names do not refer to any particular sheet.
    Without the sheet reference in the Offset/Indirect, which sheet is Excel supposed to look at when evaluating the range name? That's why it throws an error when you try to plug the range name into a chart. You will need range names that are dynamic, but nonetheless clearly identifyable. Change the references to

    count =OFFSET(INDIRECT(INDIRECT("4B!D"&1)),0,0,COUNTA(INDIRECT(INDIRECT("4B!E"&1)))-1)

    month =OFFSET(INDIRECT(INDIRECT("4B!C"&1)),0,0,COUNTA(INDIRECT(INDIRECT("4B!E"&1)))-1)

    for example, and you can create a chart that refers to 4B!month for the X axis and 4B!count as the Y values.
    Last edited by teylyn; 04-08-2011 at 07:08 AM.

  5. #5
    Registered User
    Join Date
    02-28-2011
    Location
    Ann Arbor, Michigan
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help with "Define Name" & auto-updating graphs

    The graphs I would like to create are just simple bar charts of count by month, nothing fancy. I deleted it because it was empty and not able to take the names as written. It's here in this attachment.

    Is there no way to do this with only 2 defined names? The point of the original post was to use a variable to specify the active sheet in the names, otherwise I'll be creating 80+ pairs of names. I'm confused why this appears to work fine for other functions and not graphing. For example, if I type "=SUM(count)" in a cell on sheet 4A right now it returns 17, if I type =SUM(count) in a cell on sheet 4B I get 27 - perfect, without having to manually specify the worksheet or the alter the references at all. However, that doesn't work for the graphs. Any other suggestions?
    Attached Files Attached Files

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Help with "Define Name" & auto-updating graphs

    Hmm?

    I gave this a try and it is true that you can use a dynanic named range that refers to many sheets, a tidy trick.
    However Charts don't like it, If you use a static named range, or a dynamic named range as teylyn suggests all is well.

    Edit Series > Series_values:= Names1!Count
    The workbook name must be included in this, if you don't use the workbook name Excel will reject it, remember apostrophes if the name has spaces. e.g. 'My Names 1'!count

    I can't get it to accept a named range that indirectly refers to a sheet name, and I'm sure this is the whole point of your question.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1