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
Exclude Column in Chart
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Exclude Column in Chart
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Exclude Column in Chart
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Exclude Column in Chart
Cheers Hans
I'll give it a whirl.
Oh..........
And a very Merry Christmas & Happy New Year :-)
I'll give it a whirl.
Oh..........
And a very Merry Christmas & Happy New Year :-)
Cheers ...
Dave.
Dave.
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Exclude Column in Chart
Sorry Hans
Trying to work this out, see the result with the PNG :
Trying to work this out, see the result with the PNG :
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Exclude Column in Chart
Just in case I've added the file:
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Exclude Column in Chart
You cannot use spaces in defined names. You'd need 'Week2' or 'Week_2' for example, rather than 'Week 2'
Regards,
Rory
Rory
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Exclude Column in Chart
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Exclude Column in Chart
Brilliant Hans.
Can I edit the Y range? and which menu selection gets me to open the formula?
Can I edit the Y range? and which menu selection gets me to open the formula?
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Exclude Column in Chart
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:
You can see how it's used in the chart by clicking on the series and looking at the formula bar:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England