Windows Excel Help

L

LaatSahab

Level G
Looking to create a particular functionality in Excel but don't know how to proceed in this regard. Essentially, I want to create an account automator wherein there are 3 columns, A, B, C. Column A is for Debits, B for Credits, and C for Total. The initial row for columns A and B shall be empty while column C would have a value. In subsequent rows value in Column A or B shall be added or subtracted from the value in one row above in Column C and any direct change made to the value in Column C should reflect all the remaining values below in the same column, be it any increment or decrement.
How should I go about doing this? Macros or VBA scripting and in either case, where to look for tutorials/learning material?
 
Best is to create a Macro using dummy data. U have not mentioned as to how the inputs to cells in column A & B will be given? If these are manual inputs, then Macro is best choice with dummy data. If u going to pull data from another excel file, then VBA is the way to go
 
The data in column C will be copied from another table and the inputs in Columns A and B will be either entered manually or copied from another table to verify and make necessary manipulations in Column C.
 
upload a file here with dummy data and fill couple of rows with the desired results so we can know what you want to achieve. Or you can try google sheet to share data here and make it editable to anyone with a link so people can see the possibilities of formulas or other methods like scripts (same as VBA for excel)
 
gcbeldar said:
share your email id, unable to upload excel sheet
Suppose A and B Data Entry and C is the Sum
C1 is opening opening Balance then c2 will be +C1+A2+B2 (Drag the C col)
Click to expand...
Yeah, that's about it. Either that, or I'm misunderstanding anything.

For making comparison from another sheet, you can use a combination of IF and COUNTIF if you want to find a value in a range, or INDEX-MATCH, or V/HLOOKUP functions to find values for specific dates/other criteria.

If you use Google Sheets, you can probably even use SQL if you're familiar with it.

Also, ChatGPT is kind of a great help in situations like these. Explain it clearly what you want, and it can help you out. If you can't explain it, then give some test input values and tell what the output should be and it can figure it out.
 
Here's a sample use case
Column A Column B Column C
100
2377
1592
1082
25107
Here Column C has the initial value. Any value given in Column A is subtracted(Debit) to the previous value of Column C and updated in its current row and similarly, any value in Column B is added(credited) to the previous value of Column C and updated in its current row.
Let's say if in the first value of Column C, I update it to 150 instead of 100, manually, then all the values in rows below of the same column should also increment by 50.

This is what I'm looking to do in Excel, where the data will be copied over from other sheets and thus manipulated.
 
You can use a formula as suggested by gcbeldar earlier. Assuming your first row will be opening balance the value in C1 will be fixed let say 100. Now you write the formula in C2

=IF(OR(ISNUMBER(A2),ISNUMBER(B2)),C1-A2+B2,"")

Now you can drag or copy this formula across full C column of the sheet .
 
This works as long as values are in consecutive rows. If there is one or more empty row then the formula doesn't works when the credit/debit value is entered.
 
It also doesn't really make that much sense as to why you'd have missing values in rows, where the whole point is storing credit/debit in each row.

In any case, I also think you'd be better served by a PTA software (Plain Text Accounting), like hledger or something. The advantage is that you can just type out the things, and it'll calculate daily, monthly and various other charts by itself.

It's a tradeoff, the more specialty you want, the more complex your functions will be. I've a Scooty expenses tracker which tracks my mileage, cost of fuel etc on a per-row basis. If I have to include some service or something, I just remove the formula for the next row. Later on, you can use a filter to filter out blank values and get a steady stream of values you want/need.
 
Back
Top