Help needed regarding presentation of data

E

excel_1317

Board Regular
Joined
Jun 28, 2010
Messages
212
I have a large data set from our CRM. Different people create contacts in it. So most compulsory fields are left blank or incorrect info. is added. Below is the sample data with the names of the people who created those contacts. There are around 35 people who created these. Now I want to compile a single spreadsheet and send to all these 35 people. First i thought of creating 35 tabs with names of the people who created these contacts and ask them to fill in the blank/incorrect fields. Cells with no color are filled with correct info and Red cells are the ones which need to be corrected. Please suggest how to do achieve this in professional manner without creating 35 tabs. THANKS IN ADVANCE...!!

Here is the sample data... Columns from B till P(red color) needs to be corrected..

Sheet4
ABCDEFGHIJKLMNOP
1Contact IDCreated ByAccount NameFirst NameLast NameDepartmentField of business activityJob TitleGeneric job title-Corporate/Law firms (whichever is applicable)EmailContact Address same like Account?Mailing streetMailing townMailing State/countyMailing Zip/Postal CodeMailing Country
20032000000tzB9pDavidIBA GroupBlankPatrickBlankBlankBlank
30032000000tz8TdDavidFady Sarkis Law OfficeFadySarkisBlankBlankBlankBlankBlankBlank
40032000000tyOB4DavidUAE UniversityManfredMalzahnBlankBlankBlankBlankBlankBlankBlankBlankBlankBlankBlank
50032000000tyif4DavidNony Et AssociesJeromeSartoriusBlank
60032000000tyRBIDavidStrulik SaBCarneiroBlank
70032000000tyiILDavidJuris NearshoreDanielAbitbolBlankBlankWrong info
80032000000tyQGgDavidImmunoclinEmericReynaudincomplete infoBlankBlank
90032000000tyNjyDavidUAE UniversityMohammedHussien Saleh Al-HemairyBlankBlankBlankBlankBlankBlank
100032000000txx7aJoeThx LtdErikRieglerBlankBlankBlankBlankwrong email id
110032000000ty4hpJoeFlipboardDeemaTamimiBlankBlankBlank
120032000000tyc0xJoeQuan & AssociatesNancyQuanBlankBlankBlankBlankBlankBlankBlank
130032000000tvqnxJoeRichard SheedyRichardSheedyBlankBlankBlankBlankBlankBlank
140032000000tzPwYJoeFive Apes, Inc.PatrickChiangBlankBlankBlankBlank
150032000000tyjCpJoePrysm IncPeterUllmannBlankBlankBlankBlankBlankBlankBlankBlankBlank
160032000000tybitJoeBlue Sands IPAlanSorgiBlankBlankBlankBlankBlankBlankBlankBlankBlank
170032000000tyEmEJoeVenti Group LLCTonyEichenlaubBlankBlankBlankBlankBlankBlankBlankBlankBlank
180032000000txxUrJoeThe Cheesecake Factory IncChristineVuBlankBlankBlankBlankBlankBlankBlankBlankBlank
190032000000tvrqTMariaCovington & Burling LLPMaryAnnHongBlankBlankBlank
200032000000tyqbuMariaBaker & Hostetler LLPKellyKurtzBlankBlankBlankBlankBlankBlankBlankBlankBlankBlank
210032000000tyF0EMariaELLUCIANBlankWrong infoBlankBlankBlankBlankBlankBlankBlankBlankBlank
220032000000twZWWMariaBlank Rome LLPJamieBerthaBlankBlankBlankBlankBlankBlankBlankBlankBlank
230032000000twSFKMariaFaegre Baker DanielsMickiePotterBlankBlankBlankBlank
240032000000twSEqMariaFaegre Baker DanielsMichelleDavisBlankBlankBlankBlankBlankBlankBlankBlankBlank

<tbody>
</tbody>
Excel 2007
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Hmm I don't really see the problem, I'd send that out, I send something similar every week.

The only thing I'd say is to Auto-Filter it so the person can select their own name

Simples :)
 
Upvote 0
Well, I am also sending it the same way but with 35 diffeent tab i.e. each tab for a person. But i was wondering if by using macros this spreadsheet can be designed to make the life of end user more easier and the sheet looks more organized..
 
Upvote 0
I really don't see the need and adding tabs adds complication since it makes compilation harder.

Anything that separates data from being a clearly defined table makes it a pain for putting it back together. I would also argue that nothing is more organized that a clearly laid out table - which you have.

I'd just autofilter the data dump and send it out :)
 
Upvote 0
If you really want to have unique user data without resorting to a lot of tabs you could have a userform open when the book is opened and then the user has to select their name. Once selected a macro could run the filter to only show their results.

It's basically the same as what Kyle was saying but forces the user to look at their selection only. I've had user that struggle with using autofilter (mostly because they are lazy rather than difficulty) but this would force them to do it.
 
Upvote 0
Thanks, I just want to move from simple to advanced level. Autofilter is a simple thing that anyone who knows a little of excel can apply. So by posting here I am just looking for some innovative idea..
 
Upvote 0
Hi,
I have an ideea for you.
1.you quckly transform your table in a list like this below:

ContactID Account Name Column to be filled Reason
0032000000tzB9p IBA Group First Name Blank
0032000000tzB9p IBA Group Mailing street B lank
0032000000tzB9p IBA Group Mailing State/county Blank
0032000000tyiIL Juris Nearshore Mailing street Blank
0032000000tyiIL Juris Nearshore Mailing State/county Wrong info
0032000000tyQGg Immunoclin Job Title incomplete info
0032000000tyQGg Immunoclin Mailing street Blank

2. Send this worksheet to everyone to fill their own cells in column D
3. when you receive back the file, you can use it in a vlookup formula in your initial table and in some minutes it is filled.

If you like the ideea, I will give you more details (how to transform very fast a table like a pivot back to a list, etc.)
 
Upvote 0
This is a good idea. I want to send a single file to all 35 people to let them know that what fields they missed or wrongly filled in CRM. What i am thinking is to create a excel file in which all 35 names of the people is listed and as soon as the person click on his name the information is shown on what is needed to be filled by him in CRM.

For eg. If David click on his name the Contact ID and Account name is shown and against it the fields which he need to fill and the reason whether the field is blank or wrong info..

I know this can be done in excel but dont know how..!! Plzzzz help me on this....

wizz said:
Hi,
I have an ideea for you.
1.you quckly transform your table in a list like this below:

ContactID Account Name Column to be filled Reason
0032000000tzB9p IBA Group First Name Blank
0032000000tzB9p IBA Group Mailing street B lank
0032000000tzB9p IBA Group Mailing State/county Blank
0032000000tyiIL Juris Nearshore Mailing street Blank
0032000000tyiIL Juris Nearshore Mailing State/county Wrong info
0032000000tyQGg Immunoclin Job Title incomplete info
0032000000tyQGg Immunoclin Mailing street Blank

2. Send this worksheet to everyone to fill their own cells in column D
3. when you receive back the file, you can use it in a vlookup formula in your initial table and in some minutes it is filled.

If you like the ideea, I will give you more details (how to transform very fast a table like a pivot back to a list, etc.)
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S

Forum statistics

Threads
1,214,551
Messages
6,120,159
Members
448,948
Latest member
spamiki

Share this page

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