CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Shaun Greatrix
Shaun Greatrix

Reputation: 85

Excel Named Range for a Chart Syntax?

Good Afternoon all,

I'm currently trying to dynamise the visible left and right 'sides' (start and end) of a chart by date for a report.

I've put together two defined Names by two Start and End Date cells, which is fantastic. These are simply called ReportStartDate and ReportEndDate and will return the start and end cell references for a table of data for my report.

Now I have two variables holding cell references, for example, $A$1 and $B$20 - so in theory, I should only need to place a colon between these to form a valid Series Value in the Select Data portion of my Excel Chart.

However, no luck as it returns #VALUE!. Any ideas on how to implement this properly? Many thanks.

Ps. The application of & does not help as it simply returns a typical invalid function syntax pop up.

Upvotes: 0

Views: 203

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

I think you need to look back at your named ranges and create a named range that covers all cells between ReportStartDate and ReportEndDate

For example -
if ReportStartDate is in A1 and ReportEndDate is in a cell below that you could create a third named range which I'll call FullRange:
=StartCell:INDEX(Sheet1!$A:$A,MATCH(EndCell,Sheet1!$A:$A,0))

Now, clicking on your chart series you'll get a formula appear in the formula bar that will look similar to:
=SERIES(,Sheet1!$C$1:$C$9,Sheet1!$A$1:$A$9,1)

You can replace those series with named ranges - remembering to use the workbook name as well:
=SERIES(,Sheet1!$C$1:$C$9,Book1.xlsx!FullRange,1)

Jon Peltier explains it much better here: http://peltiertech.com/chart-range-endpoints/

Upvotes: 1

Related Questions