Forum Discussion
Using the same formula to apply to multiple cells without affecting previous cells!
Hello,
I usually search google for my excel questions, but this one is tricky! It may even be beyond the scope of Excel.
In a nut shell, I'm designing a financial ledger for the handling of petty cash by my staff. Petty cash in my office is used just about daily for random, little work-related expenses, i.e., gas/travel reimbursements, office supplies, occasional lunches for meetings, etc.
Many of my employees unfortunately are careless with their handling of P/C and often cause the cash account to be come off balance.
So for this reason I now mandate that they reconcile the money at the end of each transaction not by simply deducting today's transactions from yesterday's balance, but by having them reconcile by actually counting.
I've created an excel table for that, all they need to do is count the physical number for each denomination (i.e., 1x $100 dollar bill, 3x $50 dollar bills.... 7x quarters, 46x dimes...etc) and excel then computes the total petty cash balance.
Now, what I'm trying to do is by using this simple table, I want it to post the determined balance to a ledger for each count on subsequent cells, without it changing or affecting the previously inputted balance on the above cell.
So for example, Day 1 - the table calculates a total combination of all dollars and coins of $257.53, I can have that total applied to one cell, but the next day now, Day 2 - after a transaction for some office supplies, I want that staff to again determine the balance by using the SAME table, however I want the table to display the NEW balance into the cell BELOW the previous balance and I want this to continue, so on and so fourth, WITHOUT having the previous entry affected.
Is this possible with excel? if so, HOW?
I have searched google endlessly far an wide and cannot find the solution!
Thank you!
6 Replies
Can you share the layout of your data? With "table", are you referring to an input form or an Excel Table? Are you overwriting the input of the previous person? If so, that is not a good idea. I suggest that you create a table where each transaction, i.e. money in or money out, is kept on its own row, so you can always see what the balance should have been after each transaction.
Here is a screenshot of a table I quickly put together.
Column E has a formula that calculates the running balance based on the columns "In" and "Out". Column O has a running balance based on a formula that calculates the cash counted.
Conditional formatting highlights in yellow each difference in the bills and coins from the row above. Conditional formatting also highlights if the calculated balance differs from the counting balance.
This way you can clearly see what the balance was on at any point in time and what it should be now.
I had something similar but a vertical denomination count, I want it to be able to apply the total count to a cell in a list (column) and when someone else counts at a later time, it applies the new balance to a cell below the previous, while at the same time, not affecting the previous cell's value.
In your screenshot, the denomination count only reflects the last manual entry. You don't see a history, so you don't know who goofed up when the balances are off. That is the inherent danger in overwriting existing data that contributes to a current balance.
In any case, if you want the denomination count in such a "data entry form" then you will need VBA and a trigger to move the value to the next empty table row. You could, in the same step, also keep a record of the denomination count, in the same table and hide the columns if they upset your design, but at least you'd have a history of the count.
To copy just the balance, use something like the code below. Insert a button and assign it to the macro. Each time the button is clicked, the current balance will be pushed to the next empty cell in the balance column. Adjust the references to sheets, rows and columns to suit your spreadsheet.
Sub copyDenominationBalance() Dim ws As Worksheet Dim lrow As Long Dim balCol As Long ' This is the name of the worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") countBalance = ws.Range("P20") ' this is the cell with the denomination count total balCol = 12 ' this is the column number where the table balance is. A=1, B=2, etc. L=12 'find the last row with data and add one lrow = ws.Cells(Rows.Count, balCol).End(xlUp).Row + 1 'copy the calculated balance to the table ws.Cells(lrow, balCol) = countBalance End Sub
