Struggling with Dynamic Ranges in Charts

RandomWa1k

New Member
Joined
Mar 25, 2009
Messages
10
Hey,

I'm making some charts with dynamic ranges using Series() and named ranges with offset().

While my named range for dates is working fine, the Series function isn't taking the named range with the y values. I checked the named range and its selecting the proper area. The problem is, when I enter the 'book1.xlsx'!Y_VALUE into the series function and hit 'enter,' nothing happens. I don't get an error, but I also can't lock in the formula and move on.

Any ideas here?

Thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,094
Office Version
365, 2010
Platform
Windows
Hey,

I'm making some charts with dynamic ranges using Series() and named ranges with offset().

While my named range for dates is working fine, the Series function isn't taking the named range with the y values. I checked the named range and its selecting the proper area. The problem is, when I enter the 'book1.xlsx'!Y_VALUE into the series function and hit 'enter,' nothing happens. I don't get an error, but I also can't lock in the formula and move on.

Any ideas here?

Thanks
What do you mean by "can't lock-in the formula"? Do you mean Excel won't accept it? If so, is Y_VALUE a workbook-level or sheet-level name ( got to Name Manager to determine this)? If it's sheet level, you need to reference it in the series formula like this: Sheet1!Y_VALUE.
 

RandomWa1k

New Member
Joined
Mar 25, 2009
Messages
10
What do you mean by "can't lock-in the formula"? Do you mean Excel won't accept it? If so, is Y_VALUE a workbook-level or sheet-level name ( got to Name Manager to determine this)? If it's sheet level, you need to reference it in the series formula like this: Sheet1!Y_VALUE.

Hey, thanks for getting back.

By "can't lock in" I mean, nothing happens when I hit enter. If I click outside the formula bar, it adds the cell I select to the text of the series formula.

They are all Workbook level
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
Two tricks for getting Define Name Dynamic Range Formulas into the Series Function:

1) If you are editing in the formula bar, type sheet name, then Define Name, then hit enter.

2) The other way to use step 2 of Chart Wizard on the Series tab, same trick: type sheet name, then Define Name, Click OK. In 2007 or later you have to go to Chart Tools Design Ribbon, data group, Source Data button, then same trick: type sheet name, then Define Name, Click OK.

I find it easier to use the dialog boxes instead of the Formula Bar.

Also, after you type sheet name and Defined Name, when you enter it, the sheet reference turns to the workbook name.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213

ADVERTISEMENT

For example, if I have a workbook named:

chartWorkbbok.xlsx

and a Defined name named:

DynamicRange

And a sheet named:

data

The Series function would look like this before I add the Defined Name:

=SERIES(data!$A$1,,data!$A$2:$A$4,1)

I would highlight the range part like this:

=SERIES(data!$A$1,,data!$A$2:$A$4,1)

Hit Delete key to get this:

=SERIES(data!$A$1,,data!,1)

Then type the Define Name like this:

=SERIES(data!$A$1,,data!dynamicrange,1)

After I hit enter I would get this:

=SERIES(data!$A$1,,chartWorkbbok.xlsx!DynamicRange,1)

I hope that helps!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
That usually means Excel has problems evaluating the defined name to get a range/values from it.
Hey,

I'm making some charts with dynamic ranges using Series() and named ranges with offset().

While my named range for dates is working fine, the Series function isn't taking the named range with the y values. I checked the named range and its selecting the proper area. The problem is, when I enter the 'book1.xlsx'!Y_VALUE into the series function and hit 'enter,' nothing happens. I don't get an error, but I also can't lock in the formula and move on.

Any ideas here?

Thanks
 

RandomWa1k

New Member
Joined
Mar 25, 2009
Messages
10
I have a few charts I'm making with this process and some of the named ranges work with the series function and others don't although I'm using the same basic layout for each.

Is there something I can do to help excel out here?


That usually means Excel has problems evaluating the defined name to get a range/values from it.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
Tracking down errors in a Defined Name Formula can be difficult. One trick that I use for Defined Name formulas, Conditional Formatting formulas, and Data Validation Custom formulas, is that I place them in cells before I put them in the dialog boxes. Then I can run Formula Evaluator (Alt, T, U, F) to see where the problem is. Or if Formula Evaluator does not pin-point the part of the formula that is causing the problem, I can highlight each part of the formula and use the F9 key to see if that certain part is evaluating to an error.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,616
Messages
5,512,428
Members
408,894
Latest member
Sara Russell

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top