Daily/MTD/YTD Sales Rep Tracker

[Abhimanyu]

Hi Hui/and all excel friends,

I am facing a tricky problem. Might turn out to be simple for you guys to solve.

I get 5 different excel sheets daily from 5 business unit heads. In that excel sheet, they manually put in the sales activity details for each rep. For MTD and YTD, they manually add that everyday. This manual addition is leading to errors.

I wanted to understand if there's a better way of recording the data. I am attaching a dummy excel sheet with the same format used by our BU's. It would be nice to get your help.

Regards,
Abhimanyu

 

[vletm]

Can You send samples of those 'five different excel sheets'?
... with rules, how do You 'normally' manually add ... 'something'.

 

[Abhimanyu]

Can You send samples of those 'five different excel sheets'?
... with rules, how do You 'normally' manually add ... 'something'.

Hi,

I have attached a sheet. The format is the same for all the 5 excel's that come to me. What I wanted to do is, give these guys a simpler excel to fill in their daily activity data and let the MTD and YTD nos get automatically updated.

If you would see the excel sheet, they put in the daily no on the first few columns and then add it manually in the MTD and YTD columns everyday.

I have attached the file once again.

Thanks again for your time and effort

 

[vletm]

@Abhimanyu I'm sending the 1 part of preview of an idea... for testing.
Do employees need ONLY add daily data?
Employees fill 'Daily Tracker Form' within seven days.
You can see/test a sample of form.
A layout of 'employees' sheet could be simple.
Employees could fix their data within seven days.
You (as 'operator') could get all employees data to another sheet, like Your sample sheet. This part isn't ready.
Ideas? Questions?

 

[Abhimanyu]

@Abhimanyu I'm sending the 1 part of preview of an idea... for testing.
Do employees need ONLY add daily data?
Employees fill 'Daily Tracker Form' within seven days.
You can see/test a sample of form.
A layout of 'employees' sheet could be simple.
Employees could fix their data within seven days.
You (as 'operator') could get all employees data to another sheet, like Your sample sheet. This part isn't ready.
Ideas? Questions?

Hi,

Thanks for working on this.

This looks simplistic and very easy to use.

We have 5 sales teams sitting in 5 different location. So each of this teams will have a Team Leader, who would consolidate their team's data everyday and send it across in that dummy file. This data is reviewed by me everyday to check on the daily, monthly and yearly performance at any given point of time.

The format that we have is very manual; why I say that is because when a team lead fills the data daily, he also needs to manually add the nos in MTD and YTD. I would want them to only fill in the daily data and the remaining (i.e MTD and YTD) gets calculated automatically through vlookup or some formula.

Your sheet helps. But since I am not that proficient with excel, wanted to know how do I organise this. And how do I add/change names, date and other details in that tab that you have created.

Also since you are an advance user of excel, can you advice how to get a better with this sheet.

Regards,
Abhimanyu

 

[vletm]

@Abhimanyu
I can continue working with this something like:
Your five Team Leaders should daily fill only teams 'Daily Tracker Forms'; not MTD nor YTD!
Do You get daily five files from Team Leaders? or how do You get those files?
Anyway... You could get all data automatic, including those MTD and YTD.
What informations do You really have to have on Team Leaders -file?
(Do You need something more than; Date, Name and those five values ?)
You can have more information, if needed.

 

[Abhimanyu]

@Abhimanyu
I can continue working with this something like:
Your five Team Leaders should daily fill only teams 'Daily Tracker Forms'; not MTD nor YTD!
Do You get daily five files from Team Leaders? or how do You get those files?
Anyway... You could get all data automatic, including those MTD and YTD.
What informations do You really have to have on Team Leaders -file?
(Do You need something more than; Date, Name and those five values ?)
You can have more information, if needed.

Thanks for the response.

I get 5 separate reports everyday from the 5 team leads.

- You are right; i want them to only fill the daily data. The rest I want to remain automated.

- I would need all the labels mentioned in the first dummy file. I have attached it again in this reply. Now, since the sales teams are expanding, names will keep on getting added

- As of now, I won't need additional labels apart from the one already there.

Thanks for the effort.

Abhimanyu

 

[vletm]

@Abhimanyu next versions... save all files to same folder!
Dummy Excel is for Your use - not ready.
- data shows employees daily datas
- [UpDate] updates data from Teams-files
-- Pivot, one sample to show data
-- [UpDate] refresh this sheet
--- SET Team's files names and shows previous Team-files date&time (for control) (( now DummyE2 .. E5 are just for testing (missing files)
DummyUpdate is file to update Employee Names.
- You will update names and send this file to Team Leaders for their updating
DummyE is for Team Leaders and for You to update their datas.
- for filling Daily Trackers, if Employee Name active before press the button, it will select.
- shows previous seven days data, older will cleared
- [UpDate Names] updates Employee Names.

>> I hope that You'll get some kind of image...
Pivot would show Your Daily Tracker 's datas information.
Do You need columns from A to D?
Do You want to have all Team's data to same sheet?
>>> I'll continue later more, please send comments.