Struggling with Dynamic Ranges in Charts

[RandomWa1k]

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

 

[JoeMo]

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]

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]

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]

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]

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]

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]

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.