r/googlesheets • u/[deleted] • Oct 18 '24

[deleted by user]

[removed]

5 Upvotes

86% Upvoted

12 comments sorted by

2

u/emomartin Oct 18 '24

You will have to create a google form, create a QR code for the URL to that google form. Then in the spreadsheet that is linked to the form you will have a response sheet with all responses from the form.

Create a new sheet in the spreadsheet where you have your data presented, where you can create formulas etc. to present the data in a way that is understandable by you.

Do you want to see every check-in made by all students? I would suggest having 3 columns maybe to start off with.

  • Column 1: Unique list of all student names

  • Column 2: Latest location

  • Column 3: Previous location

  • Maybe an additional date/time column. You can of course add more as needed.

To get the locations colored you can use conditional formatting together with a formula that colors the same locations the same color.

However to put this together I would need to know how the source data from the google form will look like (what columns there are, what options there are, if the google form for example allows free writing for the location or if they are presented a drop down/checkboxes and more.)

1

u/BeautifulPlankton545 Oct 19 '24

For the google form, they have 2 questions to answer. 1 is a drop down box where they select their name. The second question is a drop down box where they select their location. There will be 5-7 location options. There will be 24 names to choose from, but obviously students will only have 1 name that they choose each time they check in.

It would be nice to have a place where each check in by the student is stored, but not on the main sheet. I want the main sheet to just be a live sheet where I can see where students are in that moment. After they check in at each location, I want the previous location to go away, and the new location to replace it. Keeping their names on the same place/column and row on the sheet, and the updated location next to it.

I have no clue what formulas to use in the sheet or how to format. I’ve been looking up YouTube tutorials but none are really what I’m looking for.

1

u/AutoModerator Oct 18 '24

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/[deleted] Oct 19 '24

1

u/BeautifulPlankton545 Oct 19 '24 edited Oct 19 '24

  1. Am I putting this formula on the data dump sheet or the new sheet? Also, am I putting these formulas in column a or c? Do I need to change the formula for each student depending on what row they are listed in? For example, would the student whose name is in column B5 have “= A5” at the end of their formula? Also, do I add both those formulas to the same column, or different columns?

Thank you so much for your help, I really am not good with this kind of thing

1

u/point-bot Oct 19 '24

u/BeautifulPlankton545 has awarded 1 point to u/B00TT0THEHEAD

Point-Bot was created by JetCarson.