"Dynamic Interactive Error Graph / Chart "in excel 2007

**Aniruddha** · 08-21-2010, 06:16 AM

Hi All,

I joined the forum yesterday.

Attached sample excel file shows data of 10 types of errors made by a team of 10 engineers as pointed out by customer. Two sample column type error graphs shown have been made using normal excel charting process.

Can someone tell me how can I make a Dynamic Interactive Error Graph / Chart in excel 2007 using Scroll or Slider commands such that by moving the Sliders or Scrolls from left to right (For Engineers) and up and down For Errors ) and without using any Macros and complicated commands all possible combinations of engineers (from Mahesh to Deepak ) and errors (Error 1 to Error 10) are covered and the chart gets updated.

Thanks and regards

Aniruddha

**teylyn** · 08-22-2010, 09:14 PM

Hello,

in the attached file you'll find:

The scroll bars have been limited to a range of 1 to 10 each and the result of the selection is written into a cell: D4 for the horizontal scroll bar, C6 for the vertical scroll bar.

Then, there are these dynamic range names:

for the upper chart
Cht2Labels =OFFSET(Sheet1!$D$5,0,Sheet1!$D$4)
Cht2Data =OFFSET(Sheet1!$D$7:$D$16,0,Sheet1!$D$4)

for the lower chart
Cht1Labels =OFFSET(Sheet1!$D$5,Sheet1!$C$6+1,0)
Cht1Data =OFFSET(Sheet1!$E$5:$N$5,Sheet1!$C$6+1,0)

The range names have been inserted in the appropriate positions of the two charts' respective series and the other series in the charts have been deleted.

for the upper chart
=SERIES('Copy of Error graph.xlsx'!Cht2Labels,Sheet1!$D$7:$D$16,'Copy of Error graph.xlsx'!Cht2Data,1)

for the lower chart
=SERIES('Copy of Error graph.xlsx'!Cht1Labels,Sheet1!$E$5:$N$5,'Copy of Error graph.xlsx'!Cht1Data,1)

The conditional formatting for the min and max values in the table is a set of two rules

=E7=MIN(E$7:E$16) applies to =$E$7:$E$16 - Green
=E7=MAX(E$7:E$16) applies to =$E$7:$E$16 - Red

cheers

**Aniruddha** · 08-23-2010, 03:34 AM

Hi,

Thanks and appreciate your time and effort for replying my query.

I tried opening the file you sent expecting to get the intended results by suitably moving the slider / scroll bar but was not successful. I am reproducing two message alerts below.

Msg 1 : Security Warning : Automatic update of links has been disabled"

Msg 2: " A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, work book,range name and cell reference."

Attached image file is the screenshot for your ready reference.

Can you pl. check and advice.

Thanks and regards

Aniruddha

**teylyn** · 08-23-2010, 05:03 AM

Don't know what the issue is. Excel 2007 can act funny sometimes. I edited the SS with Excel 2010 and had no problems.

For good measure, I've saved it in Excel 2003 format as a regular .xls file. I don't see any external references, and there should not be any.

But then, there were about four dozen range names in your original file, many of them with #REF errors, others referring to unused/empty cells in Sheet1. In the attached version, I've deleted all of them. The file loads fine in Excel 2003 and I don't anticipate you'll have any errors when you open it with 2007.

cheers

**Aniruddha** · 08-24-2010, 02:28 AM

Hi,

Thanks again. The file you sent opened in excel 2007 without any problem and warning messages. The scrolls / sliders are working but possibly some small modification is required to achieve the display effect as shown in the attached jpeg file "Test 99". I am attaching a jpeg file because a powerpoint slide can't be uploaded.

Now imagine the nice visualization when the error bars from Mahesh to Deepak appear in a group every 5 seconds.

I have tested this nice visualization in " Slide show" mode with animation timing of 5 seconds.

Is it possible to achieve the same visualization effect in excel 2007 by automating sliders / scroll bars i.e. assigning a time of 3 - 5 seconds to both sliders / scroll bars? For the time being a simple white background is also ok.

Thanks and regards

Aniruddha

**teylyn** · 08-24-2010, 06:38 AM

in your first post you write

and without using any Macros and complicated commands

This is how far you get without macros and complicated commands. If you want to use a slide show effect, you will need macros.

**Aniruddha** · 08-24-2010, 08:04 AM

Hi,

Thanks for your reply. I agree I wrote that in my first post. But after reading your yesterday's post I concluded that I may not be aware of many other things that may be possible in excel 2007 but it always helps to know the capabilities from experts.

I shall be very thankful if you let me know links or excel reference material for macros and commands so that I will go through it , try it myself and contact you if there are any queries.

Thanks again for your time and effort.

Regards

Aniruddha

**teylyn** · 08-24-2010, 08:09 PM

Best would be to start a new thread in the Programming forum. Link to this one here, so people get the background. Suitable thread title would be "cycle through list of cell values every 5 seconds with VBA"