Previous post marked closed due to being unfocused, so I pared it down. Not sure if this is now good enough, if not please can someone comment to explain so I can correct it?

I'm trying to find a way to do the following in Google Sheets, but cant find anything similar after googling and browsing forums. Imagine a sheet that will end up with about 200 rows lets say, and 4 columns. Column A will have a static value (the name of a game) in each row, and different users at different times will add rows, and fill in the name of a game into Column A. Columns B, C, D represent something like "Want to Play", "Own", "Willing to Teach" and other users would then add their names to those columns to indicate interest or willingness to teach that game. This would be an ongoing process ("living document") where users will periodically add rows, update the games they own, are interested in, etc. Because its a lot of rows, and there will be multiple names already in the B, C, and D column cells, double clicking into each cell, getting the cursor into position and typing out your name would be so onerous as to scrap the idea. Below is a type of solution I sketched out, but not sure whether or how it can be executed:

3 checkboxes on each row next to the game, one for "Want to Play", "Own", and "Willing to Teach. and then a separate area with users names and a checkbox next to each name. You could check the box next to your name, and then go down the rows, selecting checkboxes within each row corresponding to the column you want your name to insert into. Once done, click a button to execute, so it would insert your name into cells based on the checkboxes you ticked. Is this doable? If so, can someone give me some tips on the types of formulas and approach I'd need to take?

Here is an example screenshot of roughly what it could look like:

Example screenshot

  • I suspect you will find that using a Google Form to collect your collaborators' information is the easiest approach to do what you need. You can then use Sheets just to wrangle the Form results (which will be collected automatically in a spreadsheet tab in 'flat' format) into the form you desire. Jun 1 at 17:32
  • Thanks for the comment. This would need to be an ongoing interactive process, so I dont think a form would do the trick (although I'm not terribly familiar with forms). Users will, in an ongoing fashion, be adding new games (rows), and adjusting the games theyre interested in, own, and are comfortable to teach. Edited post to reflect ongoing process.
    – rowie
    Jun 1 at 17:39
  • You could potentially have a 'specify additional game to add in future' question in the form to tell you which games to add to the form - editing the form doesn't erase responses made previously. Having users interact directly with the sheet opens up the possibility of multiple users trying to use it simultaneously, which is difficult to deal with in the context of the layout you have shown. Maybe doable with Apps Script (but that's beyond my skill level) Jun 1 at 18:35
  • Hi, welcome to WebApps. Are game names and number of games fixed, or added/amended or deleted? Ditto, user names and number of users? My suggestion is to visualise the data output (which might be something similar to the layout above), but create specific sheets/tabs for data management: 1) user names, 2) game names and 3) where a given user can indicate their preferences for one or more given games (using Data validation for user and game selection). A query could manage display on a fourth sheet/tab.
    – Tedinoz
    Jun 4 at 23:36


Your Answer

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

Browse other questions tagged or ask your own question.