Hello,
I have seen a lot of excellent charts on here that people have created. I am a noob when it comes to excel (can perform basic programming functions, create drop down boxes, etc.) and I am really struggling to create a spreadsheet that I would like to use to begin tracking data. I have been reading through a lot of the links here and still could not figure it out. Could someone please help?
Here is what I am looking for:
When you select one of the injury types from the drop down box, it populates a pie chart (sheet 3) with that category with the total percent of injuries (for the chart). I would like for it to do the same thing for the body part injured as well.
I would like to get more complex to where I can have bar graphs and Pareto's that utilize various columns of data as well but hopefully someone can show me how to do the basic pie charts I can figure out the more advanced ones. I appreciate any help any of you can offer!
(Document attached)
Hello & Welcome to the Board,
Care to provide any data which will be charted? There is nothing to go on here.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Hello,Originally Posted by jeffreybrown
The "data" that I would be charting is what is in the drop down boxes titled "Injury Type" and "Body Part". Because these are words, I did not know if they needed to be coded as values for graphs or? Thank you!
I understand they are words, but what do you want to graph or I should say when?
If I select Chemical burns from E4, what happens now? A pie graph that shows one Chemical burn at 100%?
Maybe you should look into a pivot chart...
http://peltiertech.com/Excel/Pivots/
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Correct- So then when you go to the next row, you select 'strain sprain' (example) and it would now be 50/50. This is to help track injuries of employees. Usually I see about 150+ reports in a year. I would like to be able to see what injuries are being distributed as well as what body parts are being injured (in pie format). My next 'phase' would be to start comparing injuries per shift, area, etc. Thank you again for helping!
Let's start with this.
Open the attachment >> enable macros
There is a worksheet change event which will build a data table in columns K:L and then on sheet 3 there is a dynamic pie chart.
Pick an injury code in E5 and see what happens.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
That appears to be doing what I had in mind for the pie chart - good work! So the big question is, how do I do this?
Well honestly with some reading and focused determination it will come.
What I have done here can be done many different ways...this is just one way.
The pie chart uses two named ranges - D_Labels and D_Count. Go to the pie chart >> right click >> select data. Look at both the series and the category and see how the dynamic names are entered.
Example: =Injuries.xlsm!D_Count
http://peltiertech.com/Excel/Charts/
http://www.andypope.info/charts.htm
http://www.tushar-mehta.com/excel/charts/
Go back to the worksheet and select Ctrl + F3. This will open the Name manager. Look for the two dynamic named ranges and in the Refers to box the formula. Check out the site below for info about establishing dynamic ranges. There are many other sites.
http://www.xldynamic.com/source/xld.LastValue.html
http://www.contextures.com/xlnames01.html
Finally, right click on the Injury Input sheet tab and view code. This is the worksheet change event which looks at column E and when a change the advanced filter code creates the data table and by default updates the pie chart at the same time since it is dynamic.
Oh yeah, one more thing...hang around forum's like this and your knowledge level will increase ten-fold.
Let me know if you have more question and I'll try my best to answer.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks