graph input formula?

[droo09]

So, I have a spreadsheet with data. I want to, preferebly, have a separate tab that has a spot for inputing "account names", and a spot for "date" that would allow the user to input a date range. After inputing the values I want a graph to show up below. The "account name" and "date" that you enter, would need to be linked with the master spreadsheet with the data. Another thing, there is a "Sales ID" column that needs to be shown on the graph, linked to the cooresponding "account name" I would really greatly appreciate any help with this.

 

[Akashwani]

Hi,

Welcome to MrExcel.

It would be helpful if you could provide some sample data.
I'm not sure what you want in your chart, but if you have data like this on your Master sheet.....

Excel Workbook
	A	B	C	D
1	SalesID	Account Names	Date	Value
2	A1234	Bill	01/05/2011	20
3	A1235	Bob	02/05/2011	100
4	A1236	Tom	03/05/2011	50
5	A1237	Sam	02/05/2011	30
6	A1238	Jo	01/05/2011	25
7	A1239	Den	03/05/2011	70
8	A1240	Don	02/05/2011	60
9	A1241	Sam	01/05/2011	40
10	A1242	Jo	02/05/2011	30
11	A1243	Den	03/05/2011	30
12	A1244	Don	04/05/2011	55
13	A1245	Bill	02/05/2011	75
14	A1246	Bob	02/05/2011	90
15	A1247	Tom	03/05/2011	25
16	A1248	Jo	04/05/2011	65
Master

You could then have something like this on your Chart sheet.....

Excel Workbook
	A	B	C	D	E
1	Account Names	Date From	Date to	*	Count
2	Bill	01/05/2011	03/05/2011	*	2
3	*	*	*	*	*
4	*	*	*	*	*
5	SalesID	Account Name	Date	Value	*
6	A1234	Bill	01/05/2011	20	*
7	A1245	Bill	02/05/2011	75	*
8	*	*	*	*	*
9	*	*	*	*	*
10	*	*	*	*	*
Chart

The formulas in A6:D6 need entering with ctrl shift enter NOT just enter.
You can then copy them down as fas as you require.

You can then build your Chart from that data returned by the formulas

Here is a link to a sample workbook for you to look at.....

droo09.xls


I hope this helps you out.

Good luck.

Ak

 

[droo09]

hi Akashwani, I really really appreciate the help! I think we are very close to where we need to be.

<TABLE style="WIDTH: 350pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=466 x:str><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 7972" width=218><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=88>date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23 width=96>Code</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 164pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23 width=218>Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23 width=64>Salesperson</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40664">5/1/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>99CCITCA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>99 CENT ONLY STORE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40664">5/1/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>A&WPANFL</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>A&W TRUCKING EXPRESS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>MP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40669">5/6/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AACFTWTX</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AMERICAN CARGO REVENUE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>CH</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40669">5/6/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AARATLGA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AARON RENTS INC</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>SJ</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40669">5/6/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AATULOK</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AMERICAN AIRLINES</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>AC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40671">5/8/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCMEMTN</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCELERATED</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>CH</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40676">5/13/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCNASTN</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCELERATED</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>CH</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40676">5/13/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCROSOH</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCCO</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>JB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 align=right x:num="40676">5/13/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCSPRGA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>ACCELLATRANS, INC</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23>JB
</TD></TR></TBODY></TABLE>


here's some sample code. It is pretty much what you had in your example. I am curious though; is there a way to generate a dynamic graph that automatically changes with the data you enter?

 

[Akashwani]

Hi,

Take a look here....

http://www.youtube.com/user/ExcelIsFun#p/search/39/7le-m8YRP6M

You will be able to create a dynamic chart based on the value in sheet Chart E2 of the sample data I provided.

Pay attention to 11 minutes onwards of the video.

Ak

 

[droo09]

Again, I really appreciate the help you are providing, you've been a life saver. I've been messing around with the dynamic chart and watching the videos for many hours, but I can't get it to work properly, not to mention I'm using Excel2003 and it seem every tutorial is made in 2007. I am trying to get the dynamic data on the left to coorespond to the dynamic chart. I used the OFFSET formula for each column, but when I make a chart, the data on the chart doesn't change properly to the data sheet.

 

[droo09]

http://www.4shared.com/document/ZDIV5NV1/Sales_ID_Temp_2.html

Here is what I have managed to conjure up so far. So, the problem seems to lie in that the graph is not portraying multiple entries with the same date correctly. Also, is there a way to auto sort it show the date in ascending order?

 

[Akashwani]

Hi,

Your file is unavailable for download as it has been deleted.

Ak

 

[droo09]

Hey, so this is the last time I'm going to bug you, I promise . I am trying to use the forumula you supplied to me, but I don't think it works for the data set I am using; it is over 3000+ rows long. Do I need tweak the formula somehow?

 

[Akashwani]

Hi,

I can't really comment without seeing the file as I'm unsure as to the problem you are having. Is the problem related to how much data you want to display in the graph? Eg, 10 weeks, or 6 months, the last 8 rows, the first 6 rows???

Ak

 

[Akashwani]

Hi,

Look at the sample file here....


droo09.xls


The chart displays XX records that are returned from the search made of the sample data and formulas that I posted earlier. I have extended the rows of data slightly (not 3000 though).

I hope this works for you.

Ak