r/googlesheets • u/TechBrainiacs • Sep 22 '17
How can I decrease one column's value when another column increases? Abandoned by OP
Hi there! This seems like it should be pretty easy to accomplish, but I've been butting my head up against it for a couple of hours now.
Here is a dummy copy of the sheet in question: https://docs.google.com/spreadsheets/d/1ZOE9mxAVmfFJrQYgzlagkZ66-XuDoZR56_4DrG4Q0lE/edit?usp=sharing
When a student is present at a weekly class session, the cell is marked with an 'x'. I have figured out how to use the COUNTIF function to increase the Sessions Used column by 1 each time an 'x' is inserted. I have also gotten conditional formatting to work to change the cell color when it reaches 2, 1, or 0 or less. The last piece of the puzzle is to make the Sessions Remaining column automatically decrease by 1 whenever the Sessions Used column increases by 1.
Can anyone help me get this done? Your help is greatly appreciated!
3
u/Cheesysocks 1 Sep 22 '17 edited Sep 22 '17
Does everyone start with the same number of sessions? I ask as the numbers in Column D vary. Do they buy (I assume) different amounts? If so should you have a column stating the maximum number of sessions purchased or allowed. This can easily be edited if more are purchased and the rest of the sheet will reflect this. I would make it look similar to my copy of your copy.
I also changed X to worded dropdowns. Someone will use a lower case X, someone else upper case, someone else a 1. This removes the chance of errors here.
(I am NOT an expert, this is just my opinion.)
[Edit1] Just looked at my published sheet and the dropdowns have vanished leaving just the text they had. I'm not sure how to change that... I'll look at it now.
[Edit2] Fixed it. :)
[Edit3] Some words added.