Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

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
Asked:
CC10
  • 2
1 Solution
 
byundtCommented:
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
 
byundtCommented:
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
 
CC10Author Commented:
Thanks very much
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now