Exclude Column in Chart

[D Willett]

I have a chart set out to record data on a week to week basis.
A2 to A18 are categories
B1 = Week 1
C1 = Week 2
D1 = Week 3
and so on..........to 52

If I create a chart based on Week 1, dead straight forward and easy.
If I want to create the same chart next week ( Week 2 ), I can't just select the data with the mouse because it will surround and include Week 1.
I assumed I could use the control key which didn't work.
The columns can be hidden but I don't want to do that, how do I create my chart and exclude any columns I don't require because I will have this issue for 52 weeks ?

Regards

[HansV]

You could put the week number in a cell, say A22.
Use Insert | Name | Define... (Excel 2003) or Fomulas tab > Define Name (Excel 2007/2010) to define a named range YValues that refers to

=Offset(SheetName!$A$2:$A$18,0,SheetName!$A$22).

where SheetName is the name of the worksheet.
Then set the Y-values range of the chart series to =SheetName!YValues.
When you change the value of A22, the chart will be updated automatically.

[D Willett]

Cheers Hans

I'll give it a whirl.

Oh..........

And a very Merry Christmas & Happy New Year :-)

[D Willett]

Sorry Hans

Trying to work this out, see the result with the PNG :

[D Willett]

Just in case I've added the file:

[rory]

You cannot use spaces in defined names. You'd need 'Week2' or 'Week_2' for example, rather than 'Week 2'

[HansV]

Hi Dave,

Names cannot contain spaces. But you weren't supposed to create a name for each week, just one name that changes dynamically. See the attached workbook. If you select a different week from cell A21, you'll see the chart change.

Delays.xls

[D Willett]

Brilliant Hans.
Can I edit the Y range? and which menu selection gets me to open the formula?

[HansV]

You can view and edit the definition of the nam YValues by selecting Insert | Name | Define... (Excel 2003) or by activating the Formulas tab of the ribbon and clicking Name Manager (Excel 2007/2010).

You can see how it's used in the chart by clicking on the series and looking at the formula bar:

x435.png

[D Willett]

Once again Hans thank you.