Create individual worksheet and merge info onto master sheet

gabe1965

New Member
Joined
Nov 23, 2014
Messages
7
Hi
I have a master sheet of all the customers that my team need to call. I want each of them (CC Name) to have their own individual worksheet containing their own accounts to chase and update. During the day I want to check their progress, so I require their notes from their individual worksheet to update my master sheet.
Can someone help?
Thanks
Gabe
PS - below is a snapshot of my spreadsheet. Sorry I do not know how to attach the actual spreadsheet as this is my first posting.

Account No.Customer NameCC NameTotal Last Called Next CallAmount ForcastedAmount PromisedPayment DateWeek/MonthQuery DateQuery TypeQuery AmountQuery Sent To
K006181SamirCarrie930.00
K008981Artillery LanetRobert1,390.08
K008188Ash Industrial John29,122.96
K008291AXIS BV John3,642.44
K008088Bargate Homes Carrie140.04
K008818Chelford Properties Amid815.28
K008829NJ Pill Amid283.14
T089Dashwood GroupCarrie613.14
K008191Dublin Bay Inns Mary1,908.00
K008888First Vintry Property Carrie98.30
K008911G Park John23,291.09
K000098Gillhams Carrie197.38
K008091GPGMary1,622.40
K002688GVASAmid5,717.54
K008828CHIPSJohn43.01
K002828ING LionbrookMary3,775.20
IK029Interserve Project Services Robert120.00
K008282J. PackmanRobert21.00
K002609John LottMary2,693.40
K268Motor N1Gary806.40
PAZX001Motor Building MaintenanceGary1,796.31
K008998Motor ConstructionGary2,057.76
K008260Kent Development Property Mary558.00
RR112Wales Diocesan Carrie884.24
K008181Lothian Pension Robert136.80
K008086LPA River of Shelton St.Robert90.00
K008889LPA River RT BellowsMary1,043.10
K008818MCD Developments Robert864.00
K006218ABC GroupJohn20.00
K008818ABC GroupJohn4,437.60
K008861ABC Group PLCJohn19,576.45
K008118ABC - CBCJohn227.18
K008688Mentor ArtCarrie948.14
K008891Metro TrustJane42,598.53
K008206MITIE Property Services John4,954.79
K008088Morrison WalesJohn7,065.71
K008209Mr HalliwellMary202.00
K002181NJ Gill & DH JohnsonAmid2,201.10
O001Oxford Village Jane21,179.70
K008189Oxford Village Direct ServicesJane13,504.77
ST289POP Management Robert114.00
P081Plus CaneJane26,202.70
K008818Questway in TownAmid631.69
K006162Remox Investments John445.44
K002122RG Magie Amid201.60
K008888Ross Freese Mary900.00
K008068SA Patient & Son in LiquidationCarrie18.00
K001066South Tyneside HomesMary387.88
K008888Standard Life Investments Property John232.50
K002968Team Group Carrie427.20
K006281Branch of South TyneCarrie835.20
T188Touchstone C P SCarrie357.22
K008081Universities Superannuation Scheme John915.46
K008188Vince Construction Carrie899.12
K008188Whatmore Robert144.00
K008088Dixon s Jane23,016.64
K008211Wrexhin ConstructionsRobert136.80
K008808Yappi Company Carrie368.58

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I think that it would be much easier to follow if we could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets.
 
Upvote 0
I think that it would be much easier to follow if we could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

Hi mumps
Thank you for your response and advice. I believe I have done what you said - please see link below:
https://app.box.com/s/c9a1nkz8w0hrg8qtslkw
This spreadsheet is for my team to log their daily calls made, when to call back the customer, log the payments promised, and the queries they have been notified of by the customer.
Currently my team filter their names in column E and then create their own spreadsheet and then they update column E to N. At the end of each day I log into each persons spreadsheet and then copy the notes into the master sheet so I can review and produce a KPI report. What I require is for excel to somehow to automatically update the master sheet without me copying them. I also need to do some form of KPI sheet which tells me and my manager how many calls each person has made and how many payment promises, queries etc they have been notified of during their day calling customers.
It would be great if columns e, f, i, k can somehow be set up so my team cannot enter wrong dates i.e. enter a date which is past due (example - today is the 23rd Nov but they enter 12th Nov).
I hope this makes sense. Any help will be greatly appreciated.
I am no expert so if you can think of anything better please include.
 
Upvote 0
Do you want only columns A to N in each of the team member's own spreadsheet or do you also want columns O, P and Q?
 
Upvote 0
Hi Gabe. Click here to download your file. You will notice that there are 2 buttons on you "Master"sheet. The "Create Sheets" button will create a sheet for each of your team members if the sheet doesn't already exist. Once you click this button, you will see the sheets. On each of these new sheets, you will see that Column R is named "Status" and each cell in the column has a drop down list where you can click "Complete" once the data for each row has been entered by the team member. The "Update Sheets" button will copy the newly entered data to your "Master" sheet. Please note that "Complete" must be chosen in column R for each completed row for this macro to work properly. You will also notice that columns E, F, I and K will not accept any date before the current date. I think that this is what you wanted. I'm not exactly sure what you would like on the "KPI" sheet when you say
I also need to do some form of KPI sheet which tells me and my manager how many calls each person has made and how many payment promises, queries etc. they have been notified of during their day calling customers.
Could you please explain in more detail referring to appropriate cells and worksheets. Please let me know how it works out.
 
Upvote 0
Hi mumps
Thank you for the spreadsheet. I will now test it will some data and see if it works - I'm sure it will!
I will let you know how it goes and if I need anything else.
Thanks again.
Gabe
 
Upvote 0
Hi mumps
I am trying to update your spreadsheet with the data from my team. How can this be done automatically without having to input the existing/live data all over again manually onto your new spreadsheet.
This will be an issue every time I update the CC chaser list.
Regards
Gabe
PS - does your spreadsheet allow me in add extra columns without affecting your macro?
 
Upvote 0
Hi Gabe. I'm sorry for the delay. I didn't get notification that you had responded. Can you copy and paste the existing data into the spreadsheet? By
update the CC chaser list
do you mean that you regularly add more names to your list? If you need to add extra columns, the macro will have to be modified to take that into account.
 
Upvote 0
Hi mumps
Thank you for replying. I have uploaded a new spreadsheet as requested.
The answer to your question is Yes – every week I run a new report which may have more names/rows. The report I ran today had 1961 rows but last week we only have 1611 rows.

Regarding columns, I may need to add extra columns because my manager may require me to show extra information but I hoping this will not happen.
Currently columns A to R is the data produced by our computer report which I run every Friday evening. Columns S to AF is the data that my team need to produce/update every day depending on how many calls they have made.

What would be great is if before I press your macro buttons “Create Sheet” and “Update Sheet” I was able to do the following:
If you could add a new sheet called NEW REPORT which would have the new report run as at today (this would only contain columns A to R). Then to have a new macro button which would look up the NEW REPORT against the existing report/spreadsheet we’ve been using and then the macro copies over the data from columns S to AF onto the new report by doing a VLOOKUP against column A (the account number). But this would mean the macro would need to accept dates which are not current.

Finally I’m not sure if this can be done but can I choose which columns it copies over onto the NEW REPORT e.g. all columns starting from S or instead can I stipulate I want to copy over comments in columns S to AF. This would be very useful just in case later I need to copy more columns e.g. S to AK or W to BB.

I hope I have explained myself clearly. Thanks again for all your help.
Gabe
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top