Forum Discussion
Using the same formula to apply to multiple cells without affecting previous cells!
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 SubI am having the same problem. However, I do not have or know how to add the developer ribbon. I am only adding two columns and then adding them to the cell above. However, I am dealing with quite a few negative numbers. I just want a basic balance column, but no matter what I put in, it won't fill down properly to the other cells in the column. Any ideas?
