chart with basic user input.

[Smithgall]

I have a chart that looks at a series of people and few peices of data. the tables used is a summary of other shets and therefore its values (not structure) change dynamically.

reporter schedule pages.xls
	B	C	D	E
22	10/12/2006
23		Gina	Georgia	Kim
24	PgsOut	0	230	0
25	PgsPD	0	0	0
26	Total	0	230	0
27	PgsIn	2899	3005	2803
28	TtlBank	2899	3235	2803
29	AvgIn	5	4	6
30	MaxOut	0	6	0
31	%PD	0%	7%	19%
32	AvgOnTPg	NA	NA	NA
33	AvgestDiff	7%	2%	5%
34	#ofjobs	61	46	42
35	Pgsperjob	48	70	67
Chart

I need to expand the table so that upto 50 people will be summarized. however some offices may use 10 people while others may use all 50 slots. My problem is that if i make a table that covers all 50 potential slots and a chart that covers all of that i may have one office with a chart that shows 10 bars and 40 "dead spaces"

What i woudl like is for these rather tecnially challenged people to choose how many slots (meaning collumn a:?) they wish to chart. then the chart simply adjust for that. Any ideas on how?

 

[acw]

G'day

Try creating a dynamic name that is based on the number of entries in row 23. The graph can then be linked to this name and will change to suit the number of entries.

HTH

Tony

 

[Smithgall]

ACW.. a litte confused

ACW, i like the sound of that but not sure what you mean or how to implement that. The first part is fine. have a cell that shows the number of entries. In fact it doesnt even have to be dynamic. some offices may wish to only chart the top 5 of 20 and other days they may wish to chart all 20 so having a cell that allows the user to choose the number of entries in row 23 is exactly what i want. But how to use that number in the chart? I am not sure how to build the chart to read that number. I wish we could past chart data on here.

 

[acw]

HI

Based on the above structure the graph will be covering the range B23:E35. Say the cell you have that contains the number of items you wish to chart is C22, then create a defined name that will cover the variable region.

Insert, Name, Define
Name: GraphData
Refers To: =OFFSET(Sheet1!$A$23,0,0,13,Sheet1!$C$22+2)

You can then have the graph refer to this name. If you change the entry in C22, then the number of columns that the graph covers will vary.

HTH

Tony

 

[Smithgall]

solved

ACW, thanks so much for the information. i used read you info and that lead me to some more education and i finally ended up with a graph with a scroll bar that the user can manipulate without having to alter the "code" to make the graph. thanks again