Help with "Define Name" & auto-updating graphs

**chrshrrsn** · 04-07-2011, 04:34 PM

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!

**sweep** · 04-07-2011, 05:03 PM

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.

**chrshrrsn** · 04-08-2011, 12:57 AM

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!

**teylyn** · 04-08-2011, 07:06 AM

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.

**chrshrrsn** · 04-08-2011, 08:09 AM

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?

**Marcol** · 04-08-2011, 08:24 AM

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.