Graph

[vinod9111]

Hi All

I have data wherein there are dates and amount in corresponding column as given below, i need to construct a graph wherein if i click on a month say April 11 it should show data for last 6 months including the current month. Is there a possibility to do so. Please help


<TABLE style="WIDTH: 115pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=153 border=0 x:str><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" width=81><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=81 height=20>Month</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>Amt</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40512">Nov-10</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40543" x:fmla="=+A2+31">Dec-10</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40574" x:fmla="=+A3+31">Jan-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40585" x:fmla="=+A6-28">Feb-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40613" x:fmla="=+A7-31">Mar-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40644">Apr-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40675" x:fmla="=+A7+31">May-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40705" x:fmla="=+A8+30">Jun-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40736" x:fmla="=+A9+31">Jul-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40766" x:fmla="=+A10+30">Aug-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="40796" x:fmla="=+A11+30">Sep-11</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>40</TD></TR></TBODY></TABLE>

regards,

Vinod

 

[Sal Paradise]

Make a dropdown using the "Forms" toolbar.

Create a Name (Insert -> Name -> Define) for your months. Let's say your data is in A:A for the months you have data for.

The name formula would read:
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

Define the name as lstMonths

Now right-click the dropdown and then double-left-click. In the "Control" Tab click in the first dialog (input range) and type =lstMonths

Set the linked cell somewhere (like under the dropdown itself) where nobody will see it, or feel free to format it as ;;;

That cell will show the number in the list of the month you took.

Let's say you put that cell in F1.

Now you need to make a few more names (Insert -> Name -> Define)

srsMonths
=IF($F$1<6,OFFSET($A$1,1,0,$F$1,1),OFFSET($A$1,1,0,6,1))

srsData
=IF($F$1<6,OFFSET($B$1,1,0,$F$1,1),OFFSET($B$1,1,0,6,1))

Now make your graph. For your series, you want to make the srsData your Y values, and you want your category values to be srsMonths

Whenever you add data or select a new month, the graph will automatically use the new data.

 

[vinod9111]

Dear Sal,

Thanx for your revert, Not familiar with forms tool bar, can you just lay down the steps bit more plainly , it would be easy for me to grasp and follow.

I could only create a name for lstmonths and somehow manage to draw the form but beyond that i am stuck.

Please help.

Make a dropdown using the "Forms" toolbar.

Create a Name (Insert -> Name -> Define) for your months. Let's say your data is in A:A for the months you have data for.

The name formula would read:
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

Define the name as lstMonths

Now right-click the dropdown and then double-left-click. In the "Control" Tab click in the first dialog (input range) and type =lstMonths

Set the linked cell somewhere (like under the dropdown itself) where nobody will see it, or feel free to format it as ;;;

That cell will show the number in the list of the month you took.

Let's say you put that cell in F1.

Now you need to make a few more names (Insert -> Name -> Define)

srsMonths
=IF($F$1<6,OFFSET($A$1,1,0,$F$1,1),OFFSET($A$1,1,0,6,1))

srsData
=IF($F$1<6,OFFSET($B$1,1,0,$F$1,1),OFFSET($B$1,1,0,6,1))

Now make your graph. For your series, you want to make the srsData your Y values, and you want your category values to be srsMonths

Whenever you add data or select a new month, the graph will automatically use the new data.

 

[Sal Paradise]

I have a Japanese version of Excel -- that won't help you much with menu names. The steps I gave are what you need to do though. I have no idea where you're stuck.

The general concept is that you can use names as the data for series in a graph. The goal is to make a name which shows the last 6 months of data, selected from a dropdown to select the end date. The steps I give above does that.