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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,673
Messages
6,137,921
Members
450,098
Latest member
Ikmal Sabri

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
Back
Top