Keeping a running balance, based on a unique identifier

EmilyE

Hello!

I am trying to figure out how to create a tracking sheet for employee Tuition reimbursements.

We allow an Annual Funding Maximum, and each time the employee submits a request, I'd like a formula to show their current available balance, based on their annual maximum. We have employee ID's as a unique identifier.

The catch is that each employee might have a different Annual Maximum amount. I am not sure how or if this is possible.

I'll attach an image of my test sheet to show what I am looking for, but I am sure it won't be this simple.

Paul Newcome

How are new entries being made for requests? Would you only want to calculate the Approved amounts? Are you wanting this to be on each row, on a single row for each student, or on a separate sheet entirely?

EmilyE

Hi Paul,

Good questions. The annual funding amount is being generated manually, but the line is populated by a form the requestor fills out.

I'd want this on each row, as the requests could come at different times during the year and wouldn't necessarily be next to each other in the sheet to facilitate a basic function.

So basically, Alysabeth (from the example) submits multiple requests, but her maximum amount for the year is 15,000, I'd like to show in each row what her current available balance is of that 15,000, based on the previous request. So after the first request, her available balance is 12,500.

Thinking this through it sounds like I might have to have a separate sheet that states the original maximum balance...

Paul Newcome

Having the separate sheet for the starting balance would certainly be very helpful. once that is set up...

[Annual Funding Max]:

=INDEX({Other Sheet Max Amount}, MATCH({Other Sheet Employee ID}, [Employee ID#]@row, 0))

[Annual Funding Remaining]:

=[Annual Funding Max]@row - SUMIFS([Approved Reimbursement Amount:]:[Approved Reimbursement Amount:], [Employee ID#]:[Employee ID#], [Employee ID#]@row)

EmilyE

Hi Paul,

I couldn't get the first formula to pull the information over. But the second formula seems to work as designed.

I will run it by the end user and see what they think! I was hoping that the first line would remain the same, and the next request would show the new balance. Currently both lines change to reflect the balance but I don't see how that could be problem... as long as we are getting the correct numbers.

Thanks so much!

Paul Newcome

Do any of your employee ID's start with a leading zero?

We can get a running total that only looks at previous entries with the help of a system generated Created (date) column.

[Annual Funding Remaining]:

=[Annual Funding Max]@row - SUMIFS([Approved Reimbursement Amount:]:[Approved Reimbursement Amount:], [Employee ID#]:[Employee ID#], [Employee ID#]@row, Created:Created, @cell <= Created@row)

EmilyE

I am not sure if they do, but I did notice that another formula I was using didn't recognize the number unless I removed the leading zero. I was simply going to tell the end user to not use the leading zero.

I'll give this a go! Thank you again!

Paul Newcome

Here is why I asked about leading zeros... If you have a leading zero then the data is a text value. If you do not have a leading zero then the data is stored as a numerical value. Inconsistent data types will almost always cause headaches.

Insert a helper column (that can be hidden to keep the sheet looking clean) and use this column formula:

[Employee ID#]@row + ""

The + "" will convert everything including the numerical values into text strings across the board. Now you have a column that has a consistent data type throughout. Reference this column in all of your formulas instead of the original column with mixed data types.