Drop down list and data copying to another sheet based on selection.

[azabetts]

Hi every one,

I need something that is quite complex and so I'm trying to explain it the best I can, apologies for any ambiguity.

So, I'm setting up a tracker for the work done on clients by employees and similarly the work that the employees have done, I need to be able to see both at a snapshot.

My idea is to have a sheet for each customer with a drop down so that the employee can select their name from a drop down box which I've set up via data validation, then explain the task that they have completed along with the time it took.

I then need this information to go to a sheet that I've set up based on their name with the same task/same time taken as they'd written on the previous sheet and populated with the customers name eg

Customer being google
Employee named Bob

So bob has done an hours worth of work on the client google in that he answered some emails.

Bob goes to the sheet named google, selects his name, writes "emails" and writes "1" based on the hours it took.

Bob then also works on client amazon (the treachery), he does so for 3 hours doing Skype calls.
So he goes to the amazon sheet, selects his name and writes skype calls 3 hours.

^that I have in place already.

So now Bob has done 4 hours of work; 3 for Amazon doing skype calls, 1 for Google doing emails.
How would I pull that information from the previous sheets into Bobs personal employee sheet using formula ?
I'm open to all suggestions.


Again Sorry if there's any ambiguity and yes in theory I could just do this manually but the more automated things are, the less chance for human error and also the more efficient it is.

Thanks in advance

Aaron

 

[AlphaFrog]

Having multiple sheets with duplicate entries is not an efficient way to do things. It gets complicated if you have to edit an entry. Then you'll have to update the duplicate entry on the other sheet as well.

I would think it would be much easier to have all your entries on one sheet in one table. Then you could easily autofilter by Client and\or employee or whatever combination of criteria you like. The SUBTOTAL function can return totals for the visible filtered entries. Or you could create summary tables to extract employee or client specific data.

 

[azabetts]

Your reply was actually a lot more helpful than you realise.

I've managed to complete what I wanted just because a single phrase you used made me think of it from another angle.

Instead of I need this data to move to here to be duplicated, I need to find a way to simplify the sheet and provide clear totals.

That I can do based on filters/conditional formatting and sum ifs.


Thanks for that :D

 

[AlphaFrog]

You're welcome. Thanks for the feedback.