Turning an excel spreadsheet into a CRM system?

Hi guys,

I have been tasked with producing a database for a new telesales person to work from. We did something similar last year but as we can't currently use a CRM system so only have excel, sql and visual studio to work with. Last year the main issue with the database they used was that it was just too big and complex, it would be laid out with columns such as Company Name | Contact Details | Date Called | Notes - but if they called someone and left notes to then ring them back another day, they would then either have to add more notes for the future call within the same cell, or create a new column for the 'second call notes'. Either way the spreadsheet was huge and messy and disorganised.

I've seen a spreadsheet which had a popup box that allowed you to enter data in a much more visually pleasing way and then after clicking 'add' would populate the spreadsheet underneath (presumably using macro/VBA). I am wondering if there is a possibility of the opposite of this? So e.g. you can click the name of the company and a popup box would generate all of their historical info (I have attached a picture that I just made in paint to try and explain how I mean as I don't have anything to work off) to show the date they were last called (which would have been inputted by the salesperson that day) and the notes from that call, as well as having the general contact details above and a space below to input new data.

I am willing to work really hard to learn what I need to in order to create this myself but I have no idea where to start or even if it is possible. Sorry that this is probably quite confusing to read and I don't have any examples to provide.

Will probably end up being a bit more complex with additional fields etc in but I want to get a grip of whether or not it is possible first.

Thank you!

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

You have to decide whether you want each "event" to be recorded as a separate record, or in different fields (columns) in one master record for each company.

If you choose the first of these, you will have multiple records for each company, but with the same (small) number of fields, with "Spoke to" and "Notes" added to the 3 fields that you show. Another column would be needed to link the first record for a company to the second, then the second to the third, and so on, but this is easily achieved with a formula. This means that several searches down the file may be needed to find all the records for one company.

If you choose the second approach, then you would have multiple triplet fields going across the worksheet, with Date, Contact and Notes repeated for each event. Thus there will be a variable number of fields for each record, and eventually you might have to decide to have a fixed maximum number of contacts. The advantage of this is that you will only have one search to find the record, and then scroll across for the events.

Obviously, the formulae needed to extract the data will vary depending on your design layout, so until you settle on that there is little specific help that can be offered.

Hope this helps.

Pete

Originally Posted by Pete_UK

You have to decide whether you want each "event" to be recorded as a separate record, or in different fields (columns) in one master record for each company.

If you choose the first of these, you will have multiple records for each company, but with the same (small) number of fields, with "Spoke to" and "Notes" added to the 3 fields that you show. Another column would be needed to link the first record for a company to the second, then the second to the third, and so on, but this is easily achieved with a formula. This means that several searches down the file may be needed to find all the records for one company.

If you choose the second approach, then you would have multiple triplet fields going across the worksheet, with Date, Contact and Notes repeated for each event. Thus there will be a variable number of fields for each record, and eventually you might have to decide to have a fixed maximum number of contacts. The advantage of this is that you will only have one search to find the record, and then scroll across for the events.

Obviously, the formulae needed to extract the data will vary depending on your design layout, so until you settle on that there is little specific help that can be offered.

Hope this helps.

Pete

I think I would want different fields rather than separate records. It needs to be easily used by someone who has little-no knowledge of excel, so needs to be easy and not possible for them to 'miss' records (I worry if there are more records for the same company, then they might miss these).

The latter is similar to what a standard excel database would look like but I don't want the person using the sheet to have to scroll across to different cells to see the notes, I almost want it transposed in a pop up box for easier viewing - with the option to then create new notes for the current date, then for this to add those new notes to another column. Next time they they 'bring up' this information, those new notes would appear again in the box as the others did, giving them a chance to then make more notes etc etc. I have attached a copy of what the underlying database may look like:

In this example I want the person using this spreadsheet to be able to see the information for 1 company at a time, and to be able to input the new notes with the date and for it to add to the necessary columns. However, I want them to be able to add more and more notes if necessary without knowing how many calls are needed, yet still have a 'final disposition' for the last call (which will be a drop down box). Then they will be able to enter 'date to call' e.g. if the person requests a callback tomorrow they can set that so the new date to call will be entered. Does this make sense? I don't mind having the huge database behind all of this (there will be about 1000s of companies to call so there will be many rows/columns) but I want a separate page or popup for the person to fill out and view the data per company so they are not overwhelmed.

@Izz

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

Originally Posted by Keebellah

This I think is more suitable for the paid services section wher you can request a price offer.
There a loads of Free CRM systems online that use an online database have looked there?

Could be - I wanted to check first before paying, if it was something I could learn about without paying for someone to complete for me then I wanted to explore that option first, hence the question! Thank you for your advice though I will have a look around.
I can't use any CRM system as my company have really strict GDPR rules and it would take too long to get it signed off to put sensitive data into these systems, needs to be internal. Likewise to complete the due diligence needed to install a CRM system into our network to use is also going to take too long for when we need this by.

Originally Posted by jmac1947

is there an expectation that there is a maximum of 3 calls per company before the final status / disposition

No sorry, that was in the example only but if someone didn't pick up for the first 3-4 times, all of those would need to be recorded. Each call could have a completely different number of attempts. I imagine I could cap it at around 10 though if needed.

Originally Posted by jmac1947

How do you envisionage the user actually using this solution in terms of selecting the right company in Col A

In an ideal world, it would be great for them to be able to click the company name or a macro which would select all the companies listed with today's date as when to call. But I also appreciate a lot of compromise will probably need to occur so whatever is the most simple way to do it. I can create a simple macro which could filter the list for today's date so that they can click that each day to see who they need to call, then not sure how it would then bring up the information needed.

Originally Posted by jmac1947

I am confident that it is all doable but a lot of questions need working through to ensure you end up where you want to be.

I am here to answer them all! Worst case scenario is I produce just the huge sheet with everything on and they will just have to work from that, but if we find any way at all to isolate the necessary information in a separate place that would be amazing. My experience with these colleagues (who also don't use excel much) is that they get overwhelmed with all the information and it causes them to pause on the phone more as they are searching for the notes to familiarise themselves

Originally Posted by dotchiejack

I made a small example, see attached.

When I open this and click it, nothing happens (content enabled and saved as a macro enabled etc). What is supposed to happen?

Originally Posted by dotchiejack

I made a small example, see attached.

Oh it is working now! I used my touch screen on my Surface and it started working. Probably just a glitch (my Surface often plays up so...)

This is incredible!! Perfect! How did you make this? This is a perfect start to what I need, is there a way I can add tweaks to it, or is it too complex for me to make any changes (as I obviously didn't know how to build it in the first place). The current date aspect refers to the date the note was made, so I'll need to change that field name to Date Called. Is there a way to add another field for 'date to call' e.g. if he spoke with company ABC and they said "could you please ring me back tomorrow?" a date could be inputted to remind him to ring them on the date specified. Then I will need to find a way to show a list of companies he needs to ring on that day. - Which can be done separately if needed (e.g. he writes 'date to call' is 07/04/2019, then on the 7th he needs to see a list with the company that he specified 07/04/2019 for. That way, if he tells someone he will call in 10 days, and 5 days later he says 5 days, he doesn't need to remember what he said as the list will present them to him on that day.) then there could be 2 date fields, 1 for when the note was made and 1 for if the company has requested a call back. Is it easy to add/change fields?

Quite easy for me to create a call today list if the field 'date to call' is then produced on the data tab, I could create some formulas/vlookups which then produce the 'call today' list for him to work from.

Thank you sooo much for this.

@Keebellah I really appreciate the help and links, but I am not allowed to use any CRM system I only have excel, SQL and visual studio. So The idea is I either make a version of one (but only needed for a call sheet, not quite as in depth as full CRM systems) or I just use a basic excel spreadsheet. But unfortunately using a CRM system is not an option. We have been pushing to allow one for the sensitive customer data that we want to use it for but it requires sign offs etc from legal, compliance etc etc and we don't see that being finalised in time for the deadline of this project (which is in 2 weeks).

Originally Posted by Keebellah

You see, you got a finger now the hand follows LOL

Hahaha so true.

Originally Posted by Keebellah

This is what it's all about once the basics are set it's a question of building, how's your VBA knowledge?

Next to 0 other than having copied and altered some VBA from google before to create new formulas. I can code in SQL only but I am determined and can allocate substantial work time to learning as much as I can with it, if it would be enough to allow me to work at this level? (I had a look at the code behind what Dotchiejack did and it looks very complex but so does any language before you learn so I have no idea what level of work this is?)

Originally Posted by Keebellah

I think you should write a list of options you want / need set then in order of priority ad sit down at the drawing board.

Now I have a starting place I think I can and will do this. Just need to know what my options are for tweaking it - whether it is something I will be able to do or get help doing (I don't want to ask too many favours or have people do my work for me as it isn't really fair on them, obviously)

You're better off not using ActiveX controls on worksheets at all. They're incredibly unstable have a look here https://www.google.co.uk/webhp?sourc...eet%20controls and by using them, you're asking for trouble.

Forms controls are much more reliable - they're in the same dropdown as the ActiveX ones, but are designed for use on worksheets - so you don't run into any stability issues, you can do most of the same things with them too and you can use them without using code.

Thank you both!!

Also weirdly think I've discovered why it doesn't work for me.. when my Surface is plugged into the DisplayPort and upto my 2 additional screens, it displays the image I attached and won't run the macro. However, if I unplug it and use just my Surface alone - works perfectly. Bizarre but good to know I should just use form buttons instead, thanks!

Hey guys, so I have been experimenting with userforms and I created a basic one which inputs data fine (all seems to be working when I run it). I have a frame with 2 option buttons in though and I wanted to make it so the user is forced to select one of them, I managed to write something which produces a msgbox if the user doesn't select one but it still creates the row of data which I don't want it to do. I've been reading a bunch of other questions and their examples don't seem to translate to mine. Not sure how to get it to not post the data without one of the buttons pressed?
Attached my sample workbook. The code though is:

 Private Sub SUBMIT_Click()

Select Case True
    Case Not OptionButton1 And Not OptionButton2
        MsgBox "Frame 1 is missing a selection. ", vbExclamation, "Missing Entry"
    
    Case Else
        'All frames have been selected
     
    
End Select

Dim lngRow As Long

Worksheets("Sheet1").Activate
lngRow = Worksheets("Sheet1").Range("A1048576").End(xlUp).Row + 1

Cells(lngRow, 1) = FirstNametxt.Value
Cells(lngRow, 2) = LastNametxt.Value
Cells(lngRow, 3) = Notestxt.Value
Cells(lngRow, 4) = MarketingStatus.Value
Cells(lngRow, 4).Activate


End Sub

I actually also want it to prevent the user posting the data unless all the fields have something in them. Any ideas?

Just remember, with VBA there isn't really a wrong way to do things. You're free to come up with whatever novel ways of using the Excel user interface you can think of. I'm a big fan of using a worksheet as a use interface rather than a userform but that's just personal preference.

The next thing I strongly urge is learn all about ADO connections and SQL commands. Storing data in Excel is flakey, you'll be served much better by using an Access database as your data store.

Strictly speaking, Me related to the current instance of the class that it resides in, not the class itself (the user form in this case), though admittedly in this case they are likely to be one and the same. If you 2 instances of the user form, Me refers to the context in which the code is exectuted.

Though that’s more difficult to understand, so just go with mc84excel’s explanation, it’s like high school physics, it’s not really what’s going on, but for a beginner it makes it easier to understand

Had a very busy couple of weeks annoyingly - doesn't help that I didn't receive the notifications of these new posts on my thread

Originally Posted by Keebellah

The last attached file works but if you need more columns a things, well, let me know and I'll explain the way it's built

Oh this looks really good!! I'm really enjoying learning VBA and 100% will continue to develop my skills after this particular project. I've got a list of things required and optional/desired things now and slightly worried that I may not have time to complete this. Came onto the forum to look at paying for someone to complete this for me (or at least enough for me to be able to make minor edits) but I notice on the post you linked, they recommend not using the commercial services. Which is a shame because I feel bad requesting your volunteering help on this (similar to how the moderator was telling the OP in the link that it's unreasonable to request this level of help for free - I totally agree), but I would feel much better paying you to help haha!

I have a basic idea of what I need and I know how to get some of it, but also seems to be making my excel crash >.<, also wonder if I can have the same updated list but used by multiple accounts just tracked to their name (they can easily type it in at the beginning, but then this would update the notes so when they search for a company in the future, they can see if it is calls they made or not. Would also be helpful to then generate their call list - something that could easily be done with vlookups and if statements to ensure people are only calling companies they specifically said they need to call on that date, but can that be updated as they update the new notes so they don't double call people in the same day????)

Originally Posted by Keebellah

If a user uses the file on his own system then he does not have to type in the name, just register the

Application.Username

This one worked perfectly! Thank you.

Do you know if there is a way to then only display data that has been submitted by that username? (e.g. if I set up a listbox or something (like in the earlier examples in this thread) to show all the notes of previous calls) If there is a column for the application.username can we filter the data that is shown to only contain theirs?

To the actual table on the separate tab? And a filter which knows which username is in use or a manual filter?

Ok so, firstly I am really appreciative of all the help that you're giving me. I have written a list of things which are required for the 1st project (I need to make a second version but not for another 5-6 weeks and I imagine I will be able to learn enough to make most of the tweaks by then). This list isn't a request for you to build it, I just want to lay out my plans partly so I have it in 1 place and partly so you can let me know if you think any parts are unrealistic or if you know where to look to learn parts if possible (e.g. what you described about autofiltering gave me good keywords to google to search and learn what I need).

I will bold what I don't think I know yet how to do well enough to complete yet. Ignoring the basics of creating a userform which opens with a click of a button etc, that's a given I think at this point :p

Project requirements:

• Time and date stamp when 'submit' button pressed

• "Date to call" field using a calendar

• No submitting data unless certain fields are complete/not empty

• Username stamp

• List of previous notes, searchable by company name or date

• List to choose from when searching for a company - no new entries, must be an existing company. This searching of the company to enter data also auto filters the list on the userform so you only see the notes for that company.

• Ability to choose a 'No more contact' option, when this is selected, the company is removed from searches and no new entries can be made for them - but really important that the data of all their calls remains in the table just not accessible from the userform if that makes sense? So I could go in and see all the data but when I lock-hide the sheet with the data, others won't be able to get to that company anymore

• Reset all fields when clicking submit

• Capture a 'disposition' of a call (i.e. failed contact, not interested, interested, requires more info), but capture this in 1 column as a number/word etc set for each disposition. Additionally, if the 'no more contact' field is also selected, this disposition should somehow be known as the final disposition for this company as is recorded as such (suppose maybe I could just relate the dispo and the 'no more contact' column and record every dispo that also has a no more contact flag?

• Wrap the text in the box that is shown in the userform so the user doesn't ever have to scroll across and only needs to scroll down if necessary

• Link the data of 'to call today' to some kind of other list within the userform so the user knows who he/she needs to ring based on what they have previously put. This would need to remain visible or have an easy button to get to/extra tab. Importantly, could they be able to click the record from this list and it autofill the 'company name' field (also thus bringing back previous records and allowing them to start making notes without having to type that name in again). It would be amazing if we could also then remove that company from the daily call list once a new record has been made for them that day (unless of course he/she selects the new 'date to call' as the same day). So e.g., company ABC asked to be called back 6th may, on the 6th the list shows this company, user clicks the name and reviews previous notes then calls and selects new callback to be for 10th as ABC is still busy. When user goes back to his/her list to ring today, this company is no longer on there but will reappear 10th.

Optional extra that I just thought of that would be really useful but not as vital:

• Telephone number associated with the company appears when the company is selected (this can be added with a vlookup or something onto the list if needed or something) - but if user rings and the company ask to be contacted on a different number in the future, user is given the option to edit the phone number, which will then change so when the record is pulled again for any future calls, the new number is given instead (also preferably a note to capture that something was changed so I know when I am reviewing the data?)

So there it is... I am aware this is a huge project and I also appreciate it isn't fair to expect/ask someone, like yourself, to dedicate much time volunteering. So I am not expecting much! Pls don't think that. If you want/can help that is amazing but not expected. Just want to lay everything out and see what you think about the requirements, if they are doable etc.

Thanks again for all your help already (and everyone who has been posting!). Your level of knowledge with this is inspiring and exactly where I want to be!

lol thanks... I take it you mean no what I am asking isn't possible then. I'll ring around some excel consultants and see if they can help. Thanks for everything else though

I already said (see post #32) that I and willing to and want to pay for the help but don't know where to turn for that. In the other thread you linked me to, the mod was saying that the paid for services here don't work and not to use them. otherwise I wanted to offer to pay you to help as I said haha.

I plan for the person within my company to lead this to be myself, and regardless of if I am able to get someone to help me design and build it for this specific project or not - I already have future training courses booked on VBA and plan to continue to develop projects like this one after anyway. It just so happens the first idea that was wanted came quickly without warning and little time. If I get a third party to help/complete this, I don't plan on relying on them to continue to make any changes etc, the campaign it's used for is almost a pilot run. Small and only 1 other person than myself will be using it, until it has proven to have been beneficial in general to the campaign and then it can be rolled out with further training and developments, by which time I won't require much third party help other than the 'help' questions as you describe.

Originally Posted by Keebellah

The way to react to this it's clear you have no idea how much coding hours it will take.
I write applications for clients too and a simple file with all kinds of conditions, exceptions and more will require more that 40 hours of programming and then testing ans correcting until you have something really workable.

Interestingly, when I first wrote this thread I actually assumed it might take a lot longer than 40 hours of programming, it was actually the responses I received with the examples people wrote and what you had specifically been saying that led me to believe others at a higher level could complete this in a days work or less.

Originally Posted by Keebellah

For starters some links, they helped me and if you Google there are more,. much more, Peltier's site, Ozgrid, Ron de Bruin, etc, etc.
https://excelvbatutor.com/index.php/...2010-tutorial/
https://www.thespreadsheetguru.com/

Thank you. I have a consultation/quote at 3pm so will see what they advise about workload for them. In the meantime I have been working on this non-stop for hours and am making good progress! Solved a few bolded bullet points now

Yup I considered them before the project, during the project and each time someone on this thread suggested it, unfortunately that aspect is still out of my hands as I described before. Getting anything done or signed off takes too long where I am and it's been tried before.

Originally Posted by Keebellah

Like I said, you can post and attach a file, we'll all gladly help along and give suggestions

I really do appreciate everyone's help. Currently I am working on getting the box where user can type the companies name to filter the listbox full of records below (the box for the user to type is actually a combo box using raw name data to choose from, and requires a match so user can't add own names). I have managed to get it to work in filtering the listbox, but it is only filtering for the 1st column and I keep trying to make changes I think should allow it to show the other columns that are associated with the rows selected but I can't seem to get it to work

I'm using

Private Sub FirstNameTxt_Change()
Dim MyList() As Variant
Dim X As Long
Dim Y As Long
Dim FoundSomething As Boolean
FoundSomething = False
Y = 0
For X = 2 To Sheets("test(izzie)").Range("A" & Rows.Count).End(xlUp).Row
     If UCase(Left(Sheets("test(izzie)").Range("A" & X).Value, Len(FirstNameTxt))) = UCase(FirstNameTxt) Then
        FoundSomething = True
        ReDim Preserve MyList(Y)
        MyList(Y) = Sheets("test(izzie)").Range("A" & X).Text
        Y = Y + 1
    End If
Next
If FoundSomething Then
    UserForm1.ListBox1.List = MyList
Else
    UserForm1.ListBox1.Clear
End If
End Sub
 
Private Sub UserForm_Activate()
    UserForm1.ListBox1.List = Range("A2:A" & Sheets("test(izzie)").Range("A" & Rows.Count).End(xlUp).Row).Value
End Sub

But I guess sometimes the problem with finding code on the internet to help with things is altering them when you don't know how they were written as such
This doc I am working on I think is actually built from one that you sent earlier haha.

I keep finding different methods of doing the same thing which makes it really difficult unless 1 method works for the whole aspect lol

Apologies, should really have explained fully.

Table 1 = record data, each calls data etc, to be seen by the user so they can familiarise themselves with what happened on the 5 calls with company X before they call them again.
Table RAW = the list of companies they are having to ring. Populates the dropdown box so they can only choose from this list and so there are only unique names in there (also when this happens in reality, they won't have any data in Table 1 as no calls yet, but table RAW would have the list of companies they will be calling at some point).

I just pasted it over whilst playing around sorry, I notice the other columns are with it but in reality wouldn't be a part of it. Would likely have the name and maybe phone number. Would be amazing if when they select a 'no further contact' option (which doesn't exist yet), that it populated the final disposition/decision in the RAW table as it's neater but I'm not sure thats possible and it isn't the most vital thing.

Also, I've been reading that having a listbox with all the fields until filtered is a bad idea if you are going to have 1000s of rows of data, so think I need to only populate the list with the result of the filter? So if they select copy ABC it will only bring up ABC data and if nothing is in the company name box, no data shows? Probably best not to overload excel in case it just crashes I guess aha

Customer database is acquired through another department (it is a cross sell so in this particular case, they are existing customers but with specific parameters for this new pilot campaign.) The list already exists and will be ready to import. It's currently in a spreadsheet

Oh ok, cool thank you! Any thoughts on the filtering?