Build a ‘customer details’ list as I go - and have it used to auto-fill new entries (CRM)

Build a ‘customer details’ list as I go - and have it used to auto-fill new entries (DIY CRM)

I am a bit of an excel noob, and my last VB experience was about 20 years ago. I honestly don’t even know what to search for on the forum here…

…but I’m just asking for very general directions – please don’t feel like I want hand holding. I can do the legwork, I’m just hoping you can point me at the right path.

--
Ok, so basically I’m trying to build a Customer Relationship Management (CRM) function into Excel.

I’ve been handed a busy Outlook inbox that gets some very complex enquiries from a pretty huge range of customers. They’ve been tracking these emails, and what’s happened with them, in an Excel spreadsheet. It’s very manual: copy/paste into the relevant fields, one by one, then select from some dropdowns for categories, and enter text to explain what’s happened so far. A lot of comms don’t get logged, and a lot of important data points haven’t been tracked.

Now, I’m trying to get them some proper CRM software, but I’m not sure if or when we could get some budget for it. In the meantime, I’m upgrading their existing system.

I’ve modified things to accept drag-drop email header information, got some simple timing functions to flag overdue action items, a few things like that.

My main problem is in reporting. I need to get reports on customer trends: the customers’ country, industry and a few other elements (associating an email address / person with a particular company, for example). All of that is doable with dropdowns, but I hate excess clicks.

What I’d like to do is have a function (a macro?) to do something like this:

1. I enter either a name or an email address into the relevant field in an Excel communication-record. Most likely simple Outlook header information (a name from the From field).
2. I click a button(?). I’m only guessing this’d be a macro.
3. Excel will check another sheet(?) to see if this customer exists.

a. If so it’ll fill in the customer details on the main sheet (leaving the user to enter or select manual fields like urgency, actions, etc).
b. If not it’ll highlight the fields, or generate a message, or whatever. The operator can then enter those details and select a second button(?), and this will add the customer to the other sheet. Future entries with this customer will auto-fill the relevant fields as per 3 a.

4. Optional extra: changes made to the customer on the customer sheet (other than their name) will update previous entries on the communication record…? Perhaps that is unwise, though. Might be better to do it manually if required. Just thinking about correcting errors in older entries.

Does that make sense?

And hey, if anyone has done something similar and has any tips at all, I’d love to hear them.

(I’m especially keen to have something like “add an action” that can log individual actions (e.g. forwarded to x; called y; replied; completed; etc) with date and time. At the moment that’s blobbed together in a single free-text field. I want to track how long things take for (for example) initial action or completion. Not sure how that would even work, though. I’ll worry about it later!)

Thanks everyone who took the time to read all of this.

Thanks very much for the offer, but I have, ahem, decided to do something simpler. I figure about a week after I leave anything complicated will break and then no-one will know how to fix it.

I'm just going to use lookup functions, keyed off the "from" field, to fill in known contacts (listed on a contacts sheet). If they see errors, they'll just need to pop over to the contacts page and update it with the new contact.

On the other hand, I'm off to ask about lookup, because I am obviously getting something terribly wrong there....

Thanks again!