• Status: Solved
• Priority: Medium
• Security: Public
• Views: 399

# dynamic range in chart

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
CC10
• 2
1 Solution

Commented:
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

Commented:
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 Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.