Solved

# dynamic range in chart

Posted on 2011-03-07
380 Views
Hello,

I have defined two ranges with the names: datePnL and DailyPnL

and formulas:
=DailyPnL!\$H\$4:OFFSET(DailyPnL!\$H\$4;0;0;COUNTA(DailyPnL!\$H:\$H)-2;0)

=DailyPnL!\$I\$4:OFFSET(DailyPnL!\$I\$4;COUNTA(DailyPnL!\$I:\$I)-2;0)

I am having a problem inputting the ranges correctly into the series bar of a chart. I have included a worksheet as an example. Can you either create a bar chart for me or instruct me on how to do it. I have tried and even got the PnL column to work but not the date column and therefore the chart would not update dynamically.
Thanks.
Chris
Example-Dynamic-Chart.xlsx
0
Question by:CC10
• 2

LVL 81

Accepted Solution

byundt earned 250 total points
ID: 35060661
Change your named range definitions to:
=DailyPnL!\$H\$4:OFFSET(DailyPnL!\$H\$4,COUNT(DailyPnL!\$H:\$H)-1,0)
=DailyPnL!\$I\$4:OFFSET(DailyPnL!\$I\$4,COUNT(DailyPnL!\$I:\$I)-1,0)

Click on the series in the chart, then modify the formula in the formula bar to:
=SERIES(DailyPnL!\$I\$3,'Example-Dynamic-Chart.xlsx'!DatePnL,'Example-Dynamic-Chart.xlsx'!DailyPnL,1)
0

LVL 81

Expert Comment

ID: 35060750
There are other constructions possible for your named range definitions:
=OFFSET(DailyPnL!\$H\$4,0,0,COUNT(DailyPnL!\$H:\$H),1)
=DailyPnL!\$H\$4:INDEX(DailyPnL!\$H\$4:\$H\$1000,COUNT(DailyPnL!\$H\$4:\$H\$1000))      'My favorite because it is not volatile

You need to enclose the name of the workbook in single quotes when using the named range in your series definitions. Excel requires either the Sheet name or Workbook name preceding the named range name.
Example-Dynamic-ChartQ26869132.xlsx
0

Author Closing Comment

ID: 35061085
Thanks very much
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question