We help IT Professionals succeed at work.

dynamic chart

Rodrigo_Briceno
Rodrigo_Briceno used Ask the Experts™
on
Hi excel experts. I was reviewing the database of previous questions made to this site and I found one interesting reference, that is closely related with the question I need to solve. The question itself referred to the use of dynamic named ranges and was applied to presenting a graph (from 1 row to more than 1,000 rows). I need something similar, but this time some changes are required. First of all the row with dates must be fixed for all the series to be presented on the graph. In second place, I need to present consecutive rows of data each time that I use the scroll (from a to e). I need a line graph for presenting the data. Attached the data. Thanks for your help.
Book1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello Rodrigo,

Where does the dynamic aspect come in? What will be dynamic, based on what? The number of rows you chart? The number of columns?

Please create a non-dynamic sample chart and then explain what you want to do with it.

Questions like these normally sit at 500 points.

cheers, teylyn

Author

Commented:
the dynamic will be based on the number of rows to show. I would like to have first graph only with id a, and then to include one by one each of the remaining rows. The thing is how to keep fixed the x axis (iwith the range of dates).

I based my points on my limited availability, but if you explain me I can change for a higher amount.
Book1.xlsx
Most Valuable Expert 2011
Awarded 2010

Commented:
So how do you want to determine which rows to show?

Author

Commented:
I would like to have a scroll bar for that. The example I am looking at defines two scroll bars, one for zoom (to increase or decrease the number of rows to show) and one for scroll (skipping rows to show).

The example that I was mentioning is in: http://www.oaltd.co.uk/DLCount/DLCount.asp?file=FunChrt7.zip

Most Valuable Expert 2011
Awarded 2010

Commented:
I'm not downloading a zip file of unknown size to see what you want to do.

The number of lines will need to be individual series. You will need to define a dynamic range name for each series you want to chart. How many will that be at any one time, maximum?

cheers

Author

Commented:
it will start with only one series and will increase 1 each time.
Most Valuable Expert 2011
Awarded 2010

Commented:
Please understand that you need to pre-define a dynamic range for EACH SERIES that you want to chart. You did not answer my question: How many lines will you want to display, max? There will need to be a series for each of the lines. They can not be created on the fly. They need to be pre-defined. Given that a line chart is not readable with hundreds of series, you may want to limit your maximum number of lines.

So, how many?

cheers, teylyn

Author

Commented:
Dear Teylyn, the purpose of the dynamic is to show all series in the graph (as a maximum), starting with 1 and ending with 6 (in the book1 example). In my real file I have 70 lines to graph, and the purpose to have all 70 is illustrative only (is not the end to have a readable file at this moment).
Cheers!
Most Valuable Expert 2011
Awarded 2010

Commented:
no can do.

Author

Commented:
OK, Teylyn. It is possible to have just one line on the graph, but by means of using a scroll to be able to change from a to b, then to c, etc. Just showing one series at a time?
Most Valuable Expert 2011
Awarded 2010
Commented:
Try this:

All series are charted, but in gray. You use the spin control arrows to cycle through the highlighted series. The highlightes series is copied into a row of its own, using an Index formula and the spin button value.

cheers, teylyn
Copy-of-Book1-1.xlsx
Most Valuable Expert 2011
Awarded 2010

Commented:
Thanks for the grade!