Cell does one thing with formula, but need to add another formula afterwards

If anybody could please help me I would be incredible appreciative.

Please refer to my current excel sheet that I have attached.

The issue is that I don't know how to show the new "# remaining" under the Sessions worksheet once a client has changed their membership type.
For example, Chad first signed up under Membership "Intro", and once it has finished, he now wants to sign up under tier 1, which gives him 4 more sessions. On the Sessions worksheet however, under the # Remaining column, it still shows the number of sessions from their previous membership...ugh this is tough.

Secondly, under the Sessions tab under "Renewal" column, I somehow need the sheet to be interactive such that when it says "Renew Membership", on the Payment tab under that clients name it will then show the new balance outstanding. Is this possible?

Sorry I'm sure this will confuse most of you, but please refer to the attached excel file to understand. This is my first post so I hope I'm doing this right and I really hope you guys can help me in any way possible.

THANK YOU
Personal Trainer Excel Sheet.xlsx

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.