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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,933
Office Version
  1. 365
  2. 2010
Platform
  1. 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,221
Office Version
  1. 365
Platform
  1. Windows
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,221
Office Version
  1. 365
Platform
  1. Windows

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,029
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,221
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,443
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top