Dynamic charting in Excel 2003

[Roberthorse]

Hi all, I would be grateful for help with the following.

I've got 4 chart lines on a chart.
On the spreadsheet the values are in 4 vertical columns A,B,C and D and there are 30 rows altogether.
The spreadsheet is set up so that variable numbers of rows contain numerical data. So any individual Column can have chart data in a variable number of rows [e.g. Row 1 to 10, Row to 1-26 etc] and there are never any empty rows between the 1st and last values listed in any Column.


But each chart line does not stop at the last value as I want it to...rather...it shows an unrecorded trend from the last value to zero.

Explanations of dynamic chart series formulae that I have found so far are not clear enough for me to understand. Can anybody help me?

[Ace_XL]

Use dynamic named ranges for each of the Columns A,B,C & D using the OFFSET function, whereby the range would determine only the continuous non-blank rows i.e. the number of rows would depend on count of non-blank continuous rows calculated in a helper cell.
Populate your chart based on the named ranges then. Perhaps a sample worksheet would aid in demonstrating better

[Roberthorse]

Thanks very much for your post.
Could you send me a sample spreadsheet with the chart on it with the OFFSET function up and running so I can get my head round it?

Regards, Rob

"Use dynamic named ranges for each of the Columns A,B,C & D using the OFFSET function, whereby the range would determine only the continuous non-blank rows i.e. the number of rows would depend on count of non-blank continuous rows calculated in a helper cell.
Populate your chart based on the named ranges then. Perhaps a sample worksheet would aid in demonstrating better"

[oeldere]

Please Login or Register  to view this content.

I can't help you with the question.

But you want help, why don't you post an example (instead of asking the forummembers to make an example).

[Ace_XL]

Perhaps something like this. If all Columns A,B,C,D have equal number of rows you could have just one cell (either of F1-I1) defining number of rows to pick.

[Roberthorse]

I attach an example of the problem. Could you edit the chart for me so it responds to new information appearing in the columns in the highlighted area without the graph lines going to zero on the right hand side? There's a cell above where you can put in a number between say 4 and 10 and see new data appear and disappear. Or if you prefer could you send me your own example so I can get my head round it?

Regards, Rob

"Use dynamic named ranges for each of the Columns A,B,C & D using the OFFSET function, whereby the range would determine only the continuous non-blank rows i.e. the number of rows would depend on count of non-blank continuous rows calculated in a helper cell.
Populate your chart based on the named ranges then. Perhaps a sample worksheet would aid in demonstrating better"

[Roberthorse]

Hi Ace-XL...Just read your post and downloaded your file [after I'd uploaded mine !] for which I thank you. The chart looks like it does exactly what I want...Unfortunately you start too high up the ladder for me! Do you know where I can find information telling me step by step how to set it up? For example I can't fathom out how the count formulae in F.1 to I.1 are linked to the Chart etc etc ?

Thanks for your assistance.

[Ace_XL]

Hi Rob,
I have amemded your file. Here's how it works..

The Count formula in row 22 of each column AG-AJ determines the number of rows which consist of continuous non-blank data for each of the columns.
Next up, there are 4 defined name ranges..fodnly named 'Range1','Range2','Range3' & 'Range4'. These determine the array for each column that needs to reflect on the chart using an OFFSET function.
Hence =OFFSET(Sheet1!$AG$25,1,0,Sheet1!$AG$22,1) would refer to the array starting 1 Row below AG25, 0 columns to the right, height equalling count in AG22 and width of 1 Column. Here is where the count in AG22:AJ22 comes in! The same logic is used in all ranges.
Next, instead of selecting series values in the chart, the named ranges are used.

Cheers

[Roberthorse]

Thanks for that Ace. Nearly there!
So, on a new copy of my spreadsheet [without your chart]

I went Insert ..Name...Define and typed in the name of the 1st Column.
Then typed in your OFFSET(......formula for the same column..........
And repeated this for all 4 columns. So far so good.....

I then went Insert ...Chart....but couldn't see how I could get the data names/formulae into the chart as this choice did not appear [to me anyway].

Sorry to bother you again but could you advise me?

Have a good one...Rob.

[Ace_XL]

Aha..When you insert the chart ..'select data' and then insert the named ranges in the 'legend entries(series)' as 'series values'. The 'series name' would be the header for each of your columns.

Am quite certain its the same in Excel 2003 too!

[Roberthorse]

Sorry, but I can't follow that..I wonder if Excel 2003 is not showing me the options that you can see?

[Ace_XL]

A little rusty with 2003 Excel..so I looked this up.

For charts in 2003 Excel, go to 'series' tab on 'source data'. You would need to update the defined name ranges under the 'values' field. This link would probably provide an insight too.

http://office.microsoft.com/en-us/ex...001109801.aspx

Hope this helps!

[Roberthorse]

Cracked it!!!!
God I'm so thick ACE-XL.
I analysed what you did and suggested ....for hours. Suddenly it dawned on me how to do it like you did.
Thanks a million for your help.
If I have any more Excel queries can I contact you?
Thanks again, Rob

[Ace_XL]

Thats cool! Please mark the thread as solved.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

I can easily vouch for there being 10s if not hundreds of forum 'gurus' who are much better equipped and knowledgable to resolve your Excel queries...so enjoy the forum Rob and use it to the fullest!

Show your appreciation..Click on the * icon if this post has been helpful

[Roberthorse]

Hi ACE, Just discovered another problem.
When I increase the number of data cells by increasing the number in the highlighted cell at the top the Category(X) Axis Labels just show 2's in all the extra categories created on the RHSide.
I right click on the chart then select Source Data and look at Category(X) Axis Labels and it appears to have the right data listed i.e the same range of cells as in the COUNT formulae in the 4 columns and the right Column category named. Could you look at your original file and tell me if it works your end? Sorry to bother you again.

[Ace_XL]

Am not sure I fully understand, however a quick tip would be to ensure that the 'count' formulae at the top of each column always represent the number of rows (in that column) that contain non-continuos data that you intend to depict on your chart. You can always increase the range of these and the chart will atomatically pick them up.

Trust this helps, else kindly upload the worksheet with the problem and i'll have a look

[Roberthorse]

Here it is then with the problem visible on the X axis

[Ace_XL]

Rob - I opened the sheet and there seems to be no problem (or so do I think!). Am i missing something?

Can you elaborate on this..

When I increase the number of data cells by increasing the number in the highlighted cell at the top the Category(X) Axis Labels just show 2's in all the extra categories created on the RHSide.

What data cells are these and where are they located? What are the 2's and the extra categories? Are you adding a 5th column by any chance?

[Roberthorse]

If you can't see it... it may be something wrong with my Excel programme or my pc. Just to make sure I attach a pic of my screen showing the ,2,2,2,2 error. Is this what you see?

[Ace_XL]

Wow..how did you manage to get that?

On further thinking, ensure that the X-Axis is automatically picked (maybe). There is probably a check-box in Excel 2003 on the 'Axes' tab under 'chart options'. Muck around with the options with the axes, you are sure to resolve it! Good luck!

[Roberthorse]

Found the source of the problem.
If I increase the number of decimal places on the X axis data it shows up like this.
Any ideas?
Just let me know if I've bothered you enough and I'll leave you alone.

Thanks, Rob

[Ace_XL]

Well in that case, keep the number of decimal places to zero. You have whole numbers on the x-axis in any case

Alternatively,you could increase the interval tick marks so that the numbers on the x-axis are more spaced out. Refer note # 3 of the below.
http://office.microsoft.com/en-us/ex...005199141.aspx

Also there is tons of help online on the microsoft website related to charts. You'd be a pro soon!