chart with basic user input.

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
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
BCDE
2210/12/2006
23GinaGeorgiaKim
24PgsOut02300
25PgsPD000
26Total02300
27PgsIn289930052803
28TtlBank289932352803
29AvgIn546
30MaxOut060
31%PD0%7%19%
32AvgOnTPgNANANA
33AvgestDiff7%2%5%
34#ofjobs614642
35Pgsperjob487067
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
May 24, 2006
Messages
68
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
May 24, 2006
Messages
68
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
 

Forum statistics

Threads
1,141,591
Messages
5,707,277
Members
421,499
Latest member
Dpbj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top