• 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.

Copy lines to other sheets with a formula

S

Shane

New Member
Hi,
Looking to sort some data out and graph it by the location / region. I think the easiest way would be to split each region up by sheet and hence looking for a formula to add (maybe a macro but I'm not too familiar with these) to each sheet to copy the rows of data I am after.

For example see attachment.
Ideally want to graph (stacked bar chat) Country vs. hours for each state per continent. Hence for the data attached have a graph for africa - containing two stacked bar charts, europe - containing two stacked bar charts, S America - containing two stacked bar charts and Austalia - containing two stacked bar charts.

Then also need to show all the data as a whole by employee vs. hours with a secondary axis by number of events (line graph). Eg: S would have total 21.7 hours by 5 events

This needs to be updated monthly and would like a bar chart graph for the month (only month 2) and then year to date (so both months 1 and 2 combined - showing total hours by employee)

I think the easiest way is to seperate the rows of data into different sheets and organise the data from there. I have tried using pivot tables but the colours for each employee are differrent and hence data is hard to compare when looking at all the graphs.

Any help would be appreciated.

Thanks

Shane
 

Attachments

Thanks Somendra.

The second graph looks great. - Is there a way of sorting this out by top 3 employees from highest number of hours. ie you would have a graph of only S L and V? (and then update this easily monthly)

The first graph however I was looking to graph it by employee not country. So Would have a seperate graph for each continent. Sorry if this wasnt clear before.

Cheers

Shane
 
@Shane

For second graph, If you click on filter you can show Top3/4 or any number based on either of the two perimeter. Secondly, the same employee filter gives you number of option for sorting.

Regards,
 
For First graph you can play with pivot table to get your required graphs. Remove country from column label and add employee in there.

Regards,
 
Yep I got it. Thanks.
Is there a way of getting the colours for each employee to stay constant? So that you an compare them easily?
 
You must log in or register to reply here.
Back
Top