I want to write only to the cells of a column “Action XP” when the respective user cells show “true” in the “Show Class” column. But I want this to be a a one way deposit-I don’t want the amount of XP to be deducted when the condition of Show Class changes to false. In other words, I want a deposit that can only be added to. I am looking at using the new Actions feature to try and make this happen but so far I don’t see a way. I can add a new row of data to the Action XP column but then its not writing to the existing rows nor filtering by the condition of Show Class true. Any ideas?
What do the “Show Class” means in this context? Can you share some screenshots of your data here? I think with conditions, this can work.
C1: Show Class is an ITE that says, if two cells are the same as each other (text) then true: If A1 is "class 2020 section 1’ and B1 is “class 2020 section 1” then TRUE. Then when I apply an amount of experience points from a number entry field into D1 (Crew XP) I want only those users who show true to get those experience points E1(if C1 TRUE then output D1 into E1.---- this part I can set up successfully(D1 is actually a LOOKUP from the sheet to which the award XP is written to). Problem is that when the condition changes from TRUE to FALSE in C1, the amount that was added previously is deducted from the total in E1 because the condition of the ITE is no longer met. I don’t know Excel very well but my guess is that there has to be an Excel solution that says: If the total in Column D changes add the new amount to E1. My goal here is that I can manage different active teams of users and when a new team is in my simulator, then the crew wide earning XP only goes to those students who are playing (Show Class TRUE)
1 Like
Ah I understand, I don’t know your full data structure, but surely you can create a row in a sheet, let’s say “Boost Points” with a structure like:
Team | Boost points
Then do a relation - lookup back to the user profiles to award that point and set that in stone?
When I use the number entry it goes into a column on an Awards Sheet. I use a look up on the Users Sheet to bring it in and then ITE to determine if the User gets those points. I don’t know how to “set it in stone” though- its changing every time the state of the user is no longer true. I am thinking now that I am going to have to use a macro or some Excel magic in the google sheet but that is beyond my very limited understanding.
So the status of the user changes over time and the points award is also changed based on your input?
Correct. The user will sometimes show True for being a member of the current team in my classroom but later when the next class comes in, that users status will not be True and when that happens, the awarded points are deducted. Trying to have it so that once the points are added, thaey are not deducted.
So what I would do is a setup like this.
First, I assume you have a classes sheet. Make a multiple relation of the class name to the users sheet and return all emails in a joined list.
Then in the Awards sheet you currently have, I assume you have a “currently in class”, which stores the class name you currently teach. Make a single relation to the classes sheet and return the joined list of emails.
Have an additional awards sheet, let’s say “Set in stone awards”, with columns for emails and award point.
You would have a button with the action add row to the sheet above, which writes the joined list of emails and points to a fixed sheet.
In the fixed award sheets, create a split text column, split by comma (or whatever you used for the joined list).
In the users sheet, create a relation matching the email to the split text column above, then a final rollup to sum the award points.
Ok thank you ThinhDinh. 
1 Like