-1

I'm looking for a simple script to update the value of a cell based on the update of another cell. This is to facilitate school staff populating next year's school calendars via a three-step process for then posting each event on a public gcal.

Here is the Google Sheet link: https://docs.google.com/spreadsheets/d/1j3KuBeRT2dQrMrIugQ3Bb9SRYwS11UqZwZuwk6kefqs/edit?usp=sharing

The two tables at the bottom of the calendar filter the entries that need action. When these are marked 'Done', I want to find that entry in the calendar and update that Status column to the next step in the process (Recorded or Final).

I need something like: =if (L76="done", [vlookup (h76, d3: ad70,-1, false) = P4 and change p4 to "Recorded"]

Google Sheets, Win10, Chrome

NSchorr
  • 875
  • 10
  • 13
Bill Warrick
  • 1
  • 1

1 Answers1

0

It's absolutely possible to mark the status column as "Recorded" or not (=IF(FILTER($L$76:$L$145, $G$76:$G$145=$A5, $H$76:$H$145=W5, $I$76:$I$145=X5)="Done", "Recorded", FALSE)), but it is not possible to have manually recorded values AND an automatically updating formula in the same cell. So to do what you want, you'll need to use Google Apps Script.

To get started, check out their tutorials and look through the documentation for manipulating spreadsheets.

Your script would need to have an onEdit trigger that fires whenever a value in the Done column becomes "Done". Then grab that range, read its values, search the calendar table for the event, and set the value.

There are lots of examples here and around the web to help you get started, and of course, you have Stack Overflow to help you figure things out as you progress with writing your script.

Diego
  • 9,261
  • 2
  • 19
  • 33
  • Great suggestion Diego, I tried a lesser version of your automatic-update formula but the table at L76 is a filter of the calendar data above so when I include an IF function in any calendar cell I get a circular reference error. I think you're right, I will need an onEdit script, but what you've outlined is above my preschool abilities. – Bill Warrick Apr 08 '18 at 09:37
  • @BillWarrick Take it slowly by starting with this very good [Codeacademy JavaScript course](https://www.codecademy.com/tracks/javascript) and then proceed with the Google Apps script tutorials. You'll be glad you did :) – Diego Apr 08 '18 at 09:49
  • Thanks Diego, hope to find that time, – Bill Warrick Apr 08 '18 at 14:58