How Can I use a form to update empty cells on an existing row?

I have a sheet that will have data that changes, in this case locker assignments.

I want to create a form for people to assign themselves to an empty locker, by providing them with a dropdown list of available lockers, and include if they're top or bottom lockers through logic. I don't want the people to have access directly to the sheet where they could accidentally remove existing assignments.

The sheet includes all locker assignments, and is organized by locker number. I don't want people to simply add rows through a form. I've attached a screenshot of a portion of the sheet, with names blocked for privacy.


  • As far as I know, anything inputted into a form will bring in a new row, it will not replace any current data.

    Best thing that I can think of is creating a locker assignment sheet w/ the locker form. This will start bringing in the data once people assign themselves a locker. Then using the form you shown above, use a Index(Collect() formula to bring in the name based on the locker number and position from the locker assignment sheet.

    Random question, when you say top or bottom locker, are the lockers stacked on top of each other? If they are, aren't they numbered separately so you wouldn't need a top or bottom?

  • Thanks for your help.

    The lockers are on top of each other, and yes they're numbered separately. People who aren't in the office will want to assign themselves a locker before returning to work, so it's easier to have it listed on each one if it's a top or bottom. We keep track for ADA accommodations, as top lockers would be too high for a wheelchair user, and bottom lockers are too difficult to get into for someone with other mobility issues. When we run out of one or the other, it will be easier to ask someone abled to switch if the need arises.

    I'm using the top/bottom section as conditional logic so the other type can be filtered out.