Perhaps not your usual, “what formula to do …” request.

I’ve a simple spreadsheet for people to fill in, basically times (in 24hr format) and names. What’s the best way of ensuring that the data is filled in correctly? I’ve no guarantee that the users will be using Excel (any version) and some definitely use Numbers or Libre/OpenOffice so I can’t rely on Excel based checking.

I’ve got an “Example” tab/sheet showing what is and isn’t OK plus help text in nearby cells along the lines of “Name only, no extra text” but I’ll still get “Joe Smith (for the first bit)” or something similar!

Is this just going to be a case of: you can lead a horse to water … ? I’d like to be able to automate the transfer of the data but if people keep messing up then that’s not going to be possible. Maybe a “How to fill out the form” document as well?

Use DATA > Data Validation to restrict entries in the time box to whatever time range you need, and lock cells you don’t want entries in (then Protect the worksheet). I’ve checked the data validation on a .xlsx file open in OpenOffice and it still works.

The time values aren’t too bad TBH, it’s actually the text cells that cause problems. The users add extraneous text (despite the various instructional text and examples) and parsing what I need from that is difficult. I.e. if the user should have entered “Joe Smith” but might have entered “Dr Joe Smith” or “Joe Smith (for the first bit)”. So having a rule that only allows one space character would cause the first to fail but not the second. Then that would also mean that double barrelled names would fail.

The thing is I’m not dealing with complete numpties here, some genuinely are doctors or similar professional people.

Could you use drop down boxes for the text in order to limit what is input?

Can you use some kind of employee ID number rather than name? Although that relies on people knowing their ID…

How about email address? That’s easier to put a rule on (mustn’t contain spaces, must contain “.” and end with “@organisation.co.uk”, with at least one character before and after the “.”.

@funkmasterp – just how big a drop down will I need for people’s names?

@bails – already have the email address in another cell.

The data being entered isn’t the name of the person filling out the form but those of helpers. There’s no way of knowing beforehand if there’ll be one or twenty (the most I’ve seen in the historical data is fifty!). If the helper is already logged in the system I don’t want to add them again but if there’s extra text I can’t do that in a programmatic way. That’s before we get to misspellings or maiden/married names!

1. Don’t use excel for a task it’s not suited for.

2. If we ignore this advice ourselves we put “warnings” in the sheet which appear in red when a user seems to have done something bad – like if there were multiple spaces in a name etc. The message will be something to the effect of “This does not look like FIRST LAST this may delay processing your form”

@poly – True but previously it was a paper form and that was even worse. This way I’ve some form of order and I don’t have to decipher peoples’ handwriting.

The Excel form has been in use for eight years now but it’s just this year that I extended it for this information and it’s proving slightly troublesome. I originally thought about a web based form but it’s public facing not an intranet so there’s security/spam concerns if I went that route.

It’s never likely to be ideal when using text in Excel as it’s not made for it.

If you have a list of names where some will have a title added then text to columns can be useful to speed up splitting out errors

The form does do some calculations on the time values entered, nothing complicated but it’s easier than doing it by hand.

There’s no list of names to compare against – well there is but it’s in a database on my computer, currently it’s over 7000 strong! The names that are entered could be amongst that number or they could be new. Not interested in titles or anything like that: if someone adds “Chris Boardman” that’s fine, “Chris Boardman MBE” isn’t. (apologies if he’s some other honour).

The thing is, I know people are trying to be helpful in adding this extra info but it gets in the way, the rest of the info I concatenate into one cell as CSV and can then just copy and paste it into the web interface to the database no problem. I’d like to do the same with this but …

At the moment I have to start typing the helper’s surname into a form which filters the list down so it’s reasonably easy to work out if they are already on the system, if they aren’t then I add them.

just how big a drop down will I need for people’s names?

I was assuming you already had the names and wanted to stop folk getting creative or adding bits when inputting, sorry.

Do they have a unique identifier e.g. staff number or user ID? You could maybe use MS Forms or do some VBA in Excel and do a simple user form.

Could you not split the data and then run some find and delete to remove unwanted things?

It’s basically free form text. The cell could/should be any valid given name, family name pair and not necessarily English. I have no way of knowing what the names could be. On average there’s ten per form with maybe half being new.

VBA is most likely unsuitable, at least 10% use Numbers, I don’t think OpenOffice/LibreOffice handle VBA either.

I’m not looking at ways of filtering out what shouldn’t be added, I’m looking for ways to encourage the user NOT to add the unwanted stuff in the first place.

Add a note to the cell in data validation for when they select it, you can put any instructions in there. Hopefully they can’t miss it then.

Data validation as others have suggested then and explicit instructions. Maybe have the top row greyed out as an example and strike through unwanted text elements in that example.

But how do you validate free form text where there is no limit to the number of valid values? A regex could filter for just two words whilst allowing for titles but would fail on double barrelled names. ^(!Dr|Mr|Mrs)([\s*\w+\s*\w+]) – or something like that.

I already have the following text in the cell above the entry rows:

“Enter the names of those who helped you in the cells below, one name per line. Each name should appear just once. No initials please: John Smith not J. Smith. No other text”

AND the first line is an example line. There’s also text (in a much bigger font) stating: “Check the Example sheet to see how to fill out and what are the potential errors.” along with said example sheet/tab which has valid lines with a green background and invalid ones with a red background.

Can you use validation to give a warning but still allow something that doesn’t meet the rules. E.g. if it’s above a certain length with more than three spaces it’s probably not a name.

Move the database onto a webserver, find someone vaguely competent in Python and HTML to knock up a web front end for it.

Can you use 2 separate columns for first name + last name? Then you can do another column that joins them together in the format that you want.

Why not have a cell for title and then just ignore it?

@Cougar – that’s a possibility but given that some people can’t get their gender correct (!) or even know which year it is I don’t hold much hope for general data integrity.

I get around 100 forms (+/- 20) per year and takes me from less than a minute to five minutes or so per form. Roughly 10% put extra text into these name fields. Perhaps not unsurprisingly the most accurately filled forms are from non-native English speakers.

@Stevious – that’s a good way of doing it. I actually do the same when creating reports from the database.

Have separate columns for title, first forename, family name, comments. Validate them to have no spaces. Ignore whatever they put in the comments – but by giving them a column for them they won’t put them anywhere else. Maybe save it for troubleshooting duplicate names, etc, but don’t routinely extract it.

Are you at liberty to just reject forms that are incorrect and tell them to do it again only properly?

Are you at liberty to just reject forms that are incorrect and tell them to do it again only properly?

Oh Yes! Essentially I’m the gatekeeper. Things are quiet now, hence going over the “problems” I’ve had and trying to head them off at the pass as it were.

Can I ask ‘why’ the names have to be absolutely correct?

I have turned up at events with a name tag “Pete will nominate someone later” printed out. That was probably the worst case but the point is, what is the data being used for and does it have to be accurate?

Dr Robert Smith
Dr Rob Smith
Robbie Smith

Does it actually matter. I spend a lot of time trying to get people to focus on data quality and data governance and people get tied up on details that possibly don’t matter. Please don’t get me started on English Address validation – House name/numer plus postcode will get the letter delivered. The rest is noise.

Sure. I’m cross referencing the helpers. So if it’s Bob Smith from Halifax on one form and Robert Smith from Halifax on another then there’s a high likelihood of it being the same person, especially if there’s other common people involved. But if Bob Smith’s from Halifax and Robert Smith is from Aberdeen then it’s unlikely.

Having extraneous and random text means I’m unable to pull names out and do a quick compare programmatically – I’d still have to manually select Bob Smith and Robert Smith but that would be from the small(ish) list of Smiths not from “Smith (my best man)”.

I’d just like to have the form such that people think: “I’ll put a name here and nothing else”. Too much to ask?

Access database?

Access database?

But an access database still lets someone type “John Smith – first part only”. Whatever tool is used you need to put some logic in to say “that’s not just a name so you need to change it” and that’s really hard. E.g. even with validation added in, how can a system tell that “, John Smith-morningonly” is saying that John Smith worked in the morning, rather than one of John’s parents had the surname “Smith” and the other had the surname “Morningonly”?

VBA userform and assume/hope most users have access to macro enabled Excel and then a simpler version for those who don’t. Submit/email button which asks user to double check or which only works/appears if all fields meet criteria.

Oh Yes! Essentially I’m the gatekeeper. Things are quiet now, hence going over the “problems” I’ve had and trying to head them off at the pass as it were.

So, right.

A few years back, I was put in charge of a department where we pre-staged – ie, configured – networking gear to send out to customers. When a job came in the project manager would release the kit to us from the warehouse and send us an ‘engineering information form’ or EIF. The EIF would contain unique details for the site, so phone number, IP addresses, DSL usernames etc. that the engineers would plug into the configs.

Except, the EIFs were always wrong. Rather than starting with a blank form the PMs would reuse an old one which meant that half the time they’d forget to update a field. The engineers would roll their eyes, correct it from the master document and amend the form.

One of the first things I did in the role was veto this. If it’s wrong, it goes back. Period. No excuses, I don’t care how urgent it might be, if it’s urgent then it’s because the PM has sat on it for a month before passing it to us so this does not impress on me a sense of accountability, it’s their own fault.

My reasoning was twofold. Firstly, this document is a line of demarcation between departments. If something is wrong because of something we’ve changed then it’s our fault for not following the documentation; if it’s wrong because it was wrong on the EIF then it’s someone else’s responsibility. Every department likes to blame another and I wanted to get to a point where the rest of the company went “oh, well it’s all going to be OK because Implementations is on the case.”

Secondly, if we keep accepting garbage then nothing ever changes. What’s even the point of the EIF if we still have to cross-reference it back to an original every time? So this is what we did. I had a few “yes but it’s just quicker to…” complaints from the engineers at first and Projects huffed and puffed for about two months because people generally take to change like ducks to petrol, but eventually (aside from a couple of wastes of DNA who were just wildly incompetent) they started checking their work more carefully and stopped routinely sending us works of fiction.