1

I have a simple spreadsheet with a date in column A, an event description in column B, a number (of hours worked) in column D, a calculated number (hours worked * hourly wage) in column E, and some other description in column F. I want my different contributors to be able to record how many hours they have worked for me at an event, and have them input data through google forms, as I do not want them to be able to access my whole spreadsheet.

So each new input through the form would result in a new row in my spreadsheet, with data for columns A,B,D and F input through Forms, and data in column E being calculated form column D. Column C is empty.

I have two questions regarding this (edit: please only answer the question marked as number 1., and when that is answered, I will move on the the next one):

  1. As column C is always either empty or filled with data manually later, after the row has been added, I need some way to make the form "skip" this column. How is this achieved?
  2. Rows and data that have already been input by contributors often need to be updated manually inside the spreadsheet, or deleted completely, rows need to be reordered, so I think the easiest solution would be to have some sort of "add-and-forget" method of adding the new rows. By this I mean having Forms just input data into the spreadsheet, and then letting me do whatever I want with this data. Is this achievable? If not through Forms, maybe through some other way?
  • For best results, please limit yourself to one question per question post. That way the answers can be focused and voted on by their own merits. – ale Jan 3 '17 at 14:12
  • Okey, thank you, so my one question, for starters would be the question marked as question number one:"As column C is always either empty or filled with data manually later, after the row has been added, I need some way to make the form "skip" this column. How is this achieved?" – Zoltán Tuba Jan 3 '17 at 14:20
  • Please edit your question. Comments are meant to be temporary. – ale Jan 3 '17 at 14:30
3

When you attach a form to a spreadsheet, the following happens:

  1. A "Form Responses" sheet gets created. Its top row has some columns bound to specific questions on the form.
  2. Each time the form is submitted, a new row is inserted in the sheet, at the end of the current data range. The form input is placed in this row, in the form-bound columns only.

The Form Responses sheet is a normal sheet with just two restrictions: (1) a form-bound column cannot be deleted; (2) the header row cannot be deleted.

So, nothing stops you from inserting a new column C for your own use in between the form-designated columns. The data you enter in the sheet manually will never be overwritten by the form, because the form input always goes into a new row inserted in the sheet. Feel free to reorder the rows, reorder the columns, delete any rows you don't want, add your own rows, add your own columns and fill them with whatever data.

  • Ah, I see, I will try this. I think my problem may have been that I tried attaching a Form to an already "working" spreadsheet. This way a new sheet got created, where all the form data goot inserted, and I could find no easy way to automatically copy the form-submitted data to my already working sheet. anyway, I will try the whole thing from scratch, thank you. – Zoltán Tuba Jan 4 '17 at 9:37
  • Okay, I have tried it, and it works - in part. There is a problem with my column E, which contains a formula (D1*5, for example). This formula is "pulled down" into the empty rows before entering any data, so as soon as let's say D2 is populated, it will automatically calculate the result in E2. When entering data through the form, this does not work, as the form creates a whole new row, where E2 is obviously empty, and does not contain the formula I need. So this looks like a dead end for me now... Do you have any suggestions? – Zoltán Tuba Jan 4 '17 at 10:43

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.