4

I have a spreadsheet used for booking employees onto training sessions. It's very simple to use; the person booking simply enters their employee number into column A and column B, C & D auto-populate the employee's first name, surname and section using VLOOKUP formulas linked to another sheet.

Column A, where the employee enters their employee number, is the only unprotected column and is subject to data validation to prevent invalid employee numbers from being entered, but here's the problem:

If a user copies and pastes their employee number into column A, often the formatting is overwritten with the source formatting where the user copied from. To make matters worse, often the data validation is overwritten too! To resolve this, would any of the following be possible?

  1. Somehow restricting the cells so that only Paste Special > values only is possible.

  2. Somehow make the cell correct it's own formatting and data validation whenever data is pasted in.

  3. Preventing copying and pasting entirely for column A.

  • 1
    A script would be ok! The only problem is my lack of scripting knowledge to achieve what I need. – Mattoverton20 Jun 12 '14 at 13:24
  • To solve the lack of scripting knowledge, go to developers.google.com/apps-script. There you will find 5-minute starts, a link to a course for beginners and a lot of information :) – Rubén May 20 '15 at 17:50
1

Does the person making the booking really need access to the spreadsheet? Would it be better to make a Google Form which they enter their number and the course into, and which then puts the data into another sheet which has all the helper columns to fill in the rest of the details.

  • If you're not a fan of the Socratic method (which I actually find to be quite helpful when the answer involves re-framing the approach), then feel free to edit ... – MaryC.fromNZ Jun 12 '14 at 10:24
  • A form is a nice idea... But booking spaces are limited. What would happen if the booking was attempted for a full session? What if the person changed their mind and wanted to remove the booking etc. – Mattoverton20 Jun 12 '14 at 13:26
  • With a spreadsheet, what is to stop the person making the booking from simply over-typing someone else's name? How do you keep a record of how many people change their minds, which can be used to predict likely cancellation rates and thus overbooking tolerances. Etc. Seriously - if you're looking for this kind of functionality, you need a proper application, not just a spreadsheet. – MaryC.fromNZ Jun 12 '14 at 14:44
  • I get what you're saying, but all I'd really like to do is make the best of what I've got. In this case, I'm having to regularly check the sheet and re-apply the formatting/data validation where its changed from a paste. I'd merely like to automate the process. – Mattoverton20 Jun 12 '14 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.