• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Flexible flow chart

A

ashuztech

New Member
Hi Chandoo & Everyone..


I am completely new here & this one is my first topic.


I have an excel sheet, which we send across the team to show weekly production report. Earlier we were using a simple chart and it was changeable, based on a Combo box (using IF statement).

now my manager is not happy with that (as usual) n wants it with quite change.

He wants to show the chart by agent name by selecting combo list and some option buttons, how can we arrange a chart which will change automatically by selecting option button or by check box?


or suppose I have 2-3 tables and I want only one chart which will change by selecting OPTION BUTTON.

e.g.- If I'll select OPTION1, chart will show me the result of TABLE 1, If I'll select OPTION 2, chart will show me the result for TABLE2. is it possible?


please check following upload.

https://skydrive.live.com/redir.aspx?cid=884c9a212345bb9a&resid=884C9A212345BB9A!107&parid=root


Please help me in this, or your advice which will help me to make this report more attractive!!

I heard about this link. and even i checked some forums. u guys are really really awesome in excel. keep it up!


Regards!

-Annen B
 
Hi Annen,


I tried to download your file and it was downloaded successfully but there is some problem for it does not open with Excel...(2007). See the window showing it a power point file while its extension is xls.. see this pic


http://www.4shared.com/photo/qPKJzgIv/999.html


Can you mail it to me at faseeh10@hotmail.com


FASEEH
 
Hi Faseeh,

I have uploaded a new file, please check.


https://skydrive.live.com/redir.aspx?cid=884c9a212345bb9a&resid=884C9A212345BB9A!109&parid=884C9A212345BB9A!107


Thanks!
 
Hi Annen,


First welcome to the Chandoo.org


I checked your report sheet, you can try with the combination of "OFFSET" and "IF" formula.

I'll upload the complete sheet once I'll done with it.


Thanks!

Atul
 
Hi Atul,


Exactly! I want to compare the data, but not between both of the process's.

the comparison between agents of the same process is required & it should be reflected by chart only. The option buttons given in the sheet will order chart to show what exactly viewer wants.


there is no relation between both of the process's. Both are different.


And thanks for your suggestion Atul, I will surely try with OFFSET.


-Annen B
 
Hi Annen,


Is it necessary to have entire table to be present in the final report or just comparison of an agent in the two countries with the options selected
 
Hi ,


Can you check out the worksheet at this link ?


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21160


Only the chart range selection based on the name from the drop-down box is done ; regarding the adding of average , rating , target , can you detail what ranges should be used for each of these ?


Narayan
 
Hi Faseeh,


Actually, its not necessary to have the table in the main tab, we just need to compare the work done by agents (not in the countries, both are different processes). but it would be better if we will have both of the data i.e. table as well as chart to show in detail.


Hi Narayan,


Thank you so much for the file you have uploaded, Its working, and too good actually.

Now, the check boxes of Target, Quality and Average is for to compare the work between all the agents. for that we will have to add one more name in drop down list i.e. "All". so that once we will get the chart for all agents we can use those check boxes as a criteria.

like I said before, if I'll select Target check box, the target bars will come up for all the agents. If I'll select Quality check box, it will show the results for quality of all the employees. and if again I want to see the chart of only one agent I will select AGENT NAME from dropdown list and his data will appear.


Thanks Faseeh & Narayan,


Annen
 
Hi Annen,


Here is the file i have been working, am a bit late....

http://www.4shared.com/file/V-3592ME/Production_Report.html


But how will you add Total Production, Target & Rating here as these are only "Points" and not "series" means when you will plot them on this graph it will show as point only!!


FASEEH
 
Hi Faseeh,


I checked with your file, Its good but the thing is there is no need to compare the data between Australia & USA. so I simply removed USA field. Now only one Productivity table is there.

I have added one more column in front of Total & Target table, so that you will come to know about the tables.

Now the chart is based on the agent name (which we are selecting from drop-down),

But how can i get the chart for all the agents to compare the data between each other? so if I add "ALL" in drop-down list, & I select it, It should be showing the chart for all agents. is it possible?


Please check updated file-

https://skydrive.live.com/redir.aspx?cid=884c9a212345bb9a&resid=884C9A212345BB9A!110&parid=884C9A212345BB9A!107


Please help me!


Regards,

Annen
 
Hi Annen,


Sorry for late reply, have a look at this file, although it is still comparing but you can choose what you really want to display on the chart, It will display only one country if you choose so but the outlook is not exactly the same as you have made in your last posted file. Have a look meanwhile i work out the file you have lastly uploaded one:


http://www.4shared.com/file/RZpg8oyE/Production_Report_11_Final.html


Regards,

FASEEH
 
Hi Annen,


I have uploaded new file with some changes & suggestions.


I have 1 suggestion to you, instead of showing all the data only in one chart you should use two charts.

1st chart will display the details of agents work for all the 5 days (which we are currently selecting by drop-down)

2nd Chart will show the details of Target, Production & Ratings.


I have removed Check Boxes and inserted 3 Option buttons.

But I am unable to link 2nd chart to those Option buttons by using offset formula as Narayan did in 1st chart. I'm so sorry for that, but if you can do it, please try with it.


please check the file-

https://skydrive.live.com/redir.aspx?cid=92bd629a339d0180&resid=92BD629A339D0180!108&parid=root


Faseeh,

your new uploaded file is awesome, great!


Thanks,

Atul Rajratna
 
Hi Annan,

See this attached file, It is exactly in-line with your requirement.

http://www.4shared.com/file/U_L_84u-/Production_Report_12_Final.html


Atul Rajratna

Thank you dear, see this one it is even better!!


Faseeh
 
Hey Faseeh,


Both the files are are really awesome. I will surely try with new one.

Thanks!


Atul,

your suggestion is too good, to show details of TARGET & AVERAGE, I'll add another chart. It looks quite better and easy to understand for agents as well.

Thanks for the suggestion dude.


but Faseeh, Narayan & Atul,

can you please link second chart with respective table? (as Narayan has used in first chart by defining cell range and by using OFFSET formula)


this is Atul's last upload-

https://skydrive.live.com/redir.aspx?cid=92bd629a339d0180&resid=92BD629A339D0180!108&parid=root


Thank you so much guys..


Annen
 
Hi ,


Is this what you were looking for ?


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21163


Narayan
 
Hi Narayan sir,


Thank you so much, finally I got it!


Can you please tell me, how you linked that chart to the cell range? I tried it by using OFFSET formula but it worked only once, then it stopped.

please tell me how to use it.


Again thanks for the file.

Thank you so much Dan, Faseeh and Atul.. Thanks a lot..


Annen
 
Hi ,


There are 3 option buttons labelled Option Button 5 , 6 and 7. These are all linked to cell AL1 ; this means that when Option Button 5 is checked , AL1 gets a value of 1 , when Option Button 6 is checked , AL1 gets a value of 2 and when Option Button 7 is checked , AL1 gets a value of 3.


This makes it easy to use the value in AL1 to lookup the proper range viz. Target , Production or Rating. This is done by defining a named range TPR_Data which refers to :


=OFFSET(Stat!$O$5:$O$12,0,Stat!$AL$1-1)


The range O5:O12 corresponds to the Target data , the range P5:p12 corresponds to the Production data and the range Q5:Q12 corresponds to the Rating data ; the Production range and the Rating range are thus offset by 1 and 2 from the Target range , which is why we subtract 1 from the value in AL1 before using it as the offset.


To get the Chart title , we use a lookup table , again using AL1 to lookup the text.


Narayan
 
You must log in or register to reply here.
Back
Top