Supermetrics Support Forum

google sheets budget formulas for debt tracking

I'm very rusty with sheets and excel so I'm not sure if what I'm trying to do is even possible (can't really imagine that it isn't for excel) or if there's an easier way to go about this? I'm trying to formulate a debt tracker for my various accounts and I can't seem to get the concept I'm looking for into a valid formula. I have two worksheets; Worksheet 1 is called 'Trans' for transactions and Worksheet 2 is called 'Plan'.


 In 'Trans', column A has values that represent either a payment or a charge to the account and column B has data validation to determine whether the value in column A is a Charge or a Payment.


In 'Plan', cell C1 holds the account max limit and cell D1 is the To-Date total I've acquired in comparison to C1.


What I'm looking for is a way to add and subtract the values in 'Trans'A based on their status of Charge or Payment determined in 'Trans'B, from 'Plan' C1 so I have a live look at my total debt on the account. Basically what a banking app does for you live, but I'm trying to combine multiple banks and accounts into a spreadsheet.


Or! If there was a way to apply conditional values of + or - to Column A based on the Data validation from Column B, I guess I can just use SUM across worksheets to add them. That seems farm more simple to me, but applying the conditional values based on the data validation is what is catching me.

  • Hi Alexandra,


    as this question is related to spreadsheet formulas, if other Supermetrics forum users do not happen to know a way on how to accomplish this, I'd suggest posting your question to Google Sheets forum at https://productforums.google.com/forum/#!topicsearchin/docs/category$3Aspreadsheets%7Csort:relevance%7Cspell:false



    Best regards,

    -Supermetrics Team

  • Thank you so much for your response! I spent about an hour trying to post to the Google sheets forum. For some reason, I can't seem to get permission to post anything? I spent anther hour trying to figure out why I couldn't do that! In the end, I was pretty disappointed in the lack of help through Google, thus I came here.
  • Hi Alexandra,


    that's unfortunate. I tested it myself and it seems to be working:

    image


    What kind of result are you getting when you click on the "new topic" -button?



    Best regards,

    -Supermetrics Team

  • I am able to fill out everything required to create a new post, but as soon as I click submit, it will load and then give me an error saying I don't have permission, but when I looked up the error # It mentioned having a private email v business, I have private, and the only other question was do I need to create an additional account for Docs or the support forum, which didn't make sense let alone I couldn't even find where I could make an account.
  • That's unfortunate. Terribly sorry that I cannot help you further with this one.


    Best regards,

    -Supermetrics Team

Login to post a comment