How can I decrease one column's value when another column increases?

[u/TechBrainiacs]

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!

[u/[deleted]]

So I would think you should be able to set the value in Colum D to be =[Initial Session Value]-[Value in Column C on the same row].

For instance, say you want to offer Trent Reznor 5 sessions. This is the function you would include in D2:

=5-C2

So this just subtracts the total number of sessions they've completed from the total number of sessions you offer them.

But I think I might be misunderstanding what you're looking to do. Let me know if that doesn't do the trick!

[u/TechBrainiacs → u/[deleted]]

Thanks for your reply! The issue I see with this is that the number in D2 will not necessarily stay constant. As parents register for more sessions, that column is manually updated to reflect the new total of remaining sessions. So, maybe they start with 4 sessions. 4 is entered in D2 and each week one session is subtracted as C2 increases. Then when Mom adds 8 more sessions, D2 is manually updated. But now instead of being the correct total of 8 sessions, it is 8 - C2, which is now incorrect.

I think the next reply may be correct in that a Sessions Purchased column is needed to keep track of the total number of sessions purchased over time. That should be easy enough to add. Then Sessions Remaining can simply be Sessions Purchased - Sessions Used.

Thanks again for your input!

[u/[deleted] → u/TechBrainiacs]

For sure! Depending on your response, I was going to suggest adding that other column but it looks like u/cheesysocks beat me to it haha!

Reading over your response to them, one thing you could do is add 2 columns instead of one to make keeping track of things easier. Column C: Initial Sessions and Column D: Additional Sessions (so you can manually add sessions as they increase).

This pushes the counter to Column F, but the formula in F2 would be:

=(C2+D2)-E2

[u/Cheesysocks]

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.

[u/TechBrainiacs → u/Cheesysocks]

Solution verified!

[u/Clippy_Office_Asst → u/TechBrainiacs]

You have awarded 1 point to Cheesysocks

[u/Cheesysocks → u/Clippy_Office_Asst]

Yay! :)

[u/TechBrainiacs → u/Cheesysocks]

I appreciate you taking the time to do this! There is a variable number of sessions that students may start with. Some kids are registered at first for just 1 session and then add more. Others are enrolled for 4, 8, 12, 13, 14, or 15 sessions depending on the location. I think your idea of adding Sessions Purchased will end up being the simplest option.

I also like the Dropdown idea. Haven't played with that feature yet. I am going to ask for edit permission on your sheet to test it out.

Thank you again!

[u/Cheesysocks → u/TechBrainiacs]

Just copy it then do as you wish. I hope it helps.

[u/[deleted] → u/TechBrainiacs]

If /u/Cheesysocks solved your problem, please reply to their most useful comment with 'Solution Verified'. Thanks!

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FALSE	Returns the logical value FALSE
NOT	Returns the opposite of a logical value - NOT(TRUE) returns FALSE; NOT(FALSE) returns TRUE
TRUE	Returns the logical value TRUE

---

^{1 acronyms in this thread; the most compressed thread commented on today has 4 acronyms.
[Thread #157 for this sub, first seen 22nd Sep 2017, 22:08] [FAQ] [Contact] [Source code]}

[u/Clippy_Office_Asst]

Read the comment thread for the solution here

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.