2

Firstly, to help explain what I'm looking for. We're a charity delivering services each week to children and young people. At the end of our service, a volunteer will fill out a report form on each child. Some of that form is selecting a number between 1-5 to measure their development, such as 'physical wellbeing' and 'social wellbeing'.

So, I have inherited a large google sheet that contains the above information. Each child is listed multiple times (as people report weekly) and there are columns that display the numbers chosen by the volunteer (1-5) in physical and social wellbeing.

  • Is it possible to select that child's info and plot their development over time on a graph?
  • How do I do that, please? The sheet also contains the date of each service.
1
  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Jan 13, 2023 at 15:03

1 Answer 1

1

Choose Insert > Sheet and put this formula in cell B1 of the new sheet:

=query( 
  'Form Responses 1'!A1:N, 
  "select N, toDate(A), P, S 
   where N = 'Jane' ", 
  1 
)

...where A is the timestamp column, N is the name column, and P and S are the metrics columns you want to plot. Then select columns C:E and choose Insert > Chart.

To easily switch from child to another, select cell A1 and choose Insert > Dropdown. In the Data validation rules sidebar, choose Criteria > Dropdown (from a range) and put 'Form Responses 1'!N2:N in the range box. Then modify the where N is 'Jane' ", clause of the query() formula like this:

where N = '" & A1 & "' ",

4
  • Hello, thanks very much for your reply. I am being very stupid and not understanding- could you help? My sheet looks like this example: Date Name Active Wellbeing Social Wellbeing 02/10/2021 Child 1 5 4 09/10/2021 Child 1 5 4 16/10/2021 Child 1 4 3 23/10/2021 Child 1 5 4 20/11/2021 Child 1 5 5 06/11 /2021 Child 1 4 3 11/12/2021 Child 1 5 3 22/01/2022 Child 1 5 4 05/02/2022 Child 1 5 3 Could you help me input that into the answer you gave me? I want it to plot Child 1's progress (or not) for all dates onto a chart. Thank you Commented Jan 15, 2023 at 20:03
  • Follow the instructions in the answer. Do not post sample data in a comment. If you do not want to share a sample spreadsheet, edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Commented Jan 15, 2023 at 20:06
  • There are lots of other children's names (with similar dates) in the sheet and I need to do same for all of them. Commented Jan 15, 2023 at 20:07
  • The query() formula in the answer gets the metrics for all names in column N. Make your question answerable by inserting a table or by sharing a sample spreadsheet. Commented Jan 15, 2023 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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