Forum Discussion
LionKing9450
Dec 26, 2024Copper Contributor
Personal Budget Template issue
Hi all. I have created a person budget template for my own use.
The below (A) shows the page on transaction details for December 2024.
(A) Transaction Details - December 2024
The below shows the sample of Cash on Hand Account.
(B) Cash on Hand - Sample
(A) is the masterlist, where (B) is the result list that only showcase Cash on Hand related transactions. For (B), All these 4 columns have formulas to reflect the result (ie. Date, Description/Account related to the expenses or income, Debit and/or credit).
Date formula: =IFS('TRANSACTION DETAILS - SAMPLE'!D13='TRANSACTION DETAILS - SAMPLE'!$J$6,'TRANSACTION DETAILS - SAMPLE'!A13,'TRANSACTION DETAILS - SAMPLE'!E13='TRANSACTION DETAILS - SAMPLE'!$J$6,'TRANSACTION DETAILS - SAMPLE'!A13,'TRANSACTION DETAILS - SAMPLE'!D13<>'TRANSACTION DETAILS - SAMPLE'!J12,"",'TRANSACTION DETAILS - SAMPLE'!E13<>'TRANSACTION DETAILS - SAMPLE'!$J$6,"")
Description/Account formula: =IFS('TRANSACTION DETAILS - SAMPLE'!D13='TRANSACTION DETAILS - SAMPLE'!$J$6,'TRANSACTION DETAILS - SAMPLE'!E13,'TRANSACTION DETAILS - SAMPLE'!E13='TRANSACTION DETAILS - SAMPLE'!$J$6,'TRANSACTION DETAILS - SAMPLE'!D13,'TRANSACTION DETAILS - SAMPLE'!D13<>'TRANSACTION DETAILS - SAMPLE'!$J$6,"",'TRANSACTION DETAILS - SAMPLE'!E13<>'TRANSACTION DETAILS - SAMPLE'!$J$6,"")
Debit: =IF('TRANSACTION DETAILS - SAMPLE'!D14='TRANSACTION DETAILS - SAMPLE'!$J$6,'TRANSACTION DETAILS - SAMPLE'!F14,"")
Credit: =IF('TRANSACTION DETAILS - SAMPLE'!E15='TRANSACTION DETAILS - SAMPLE'!$J$6,'TRANSACTION DETAILS - SAMPLE'!F15,"")
Those that you have seen as empty, is because the transactions at that particular line does not involve cash on hand.
My problem is, how I can make them without blanks or skip blanks and move on to the next cell that have cash on hand when the formula involve <>?
- LionKing9450Copper Contributor
My column G is to separate Transaction Details and Chart of Accounts, Hence I would not want it to be in use. However, I still don't undertand. Is there anyway I can attach my files here for anyone to test it out for me?
Try below:
1. Add a Helper Column: In your "Transaction Details" sheet, add a helper column to identify rows that involve "Cash on Hand".
For example, in column G (assuming it's empty), use the following formula to mark relevant rows:
=IF(OR(D13=$J$6, E13=$J$6), ROW(), "")
2. Create a Sequential List of Row Numbers: In another column (say, column H), create a sequential list of row numbers for the relevant transactions.
Use this formula in H13 and drag it down:
=IF(G13<>"", COUNTIF($G$13:G13, ">0"), "")
3. Use INDEX and MATCH to Populate the "Cash on Hand" List: In your "Cash on Hand" sheet, use the INDEX and MATCH functions to pull the relevant data without blanks.
For the Date column in "Cash on Hand" (B):
=IFERROR(INDEX('TRANSACTION DETAILS - SAMPLE'!A:A, MATCH(ROW(A1), 'TRANSACTION DETAILS - SAMPLE'!H:H, 0)), "")
For the Description/Account column:
=IFERROR(INDEX('TRANSACTION DETAILS - SAMPLE'!E:E, MATCH(ROW(A1), 'TRANSACTION DETAILS - SAMPLE'!H:H, 0)), "")
For the Debit column:
=IFERROR(INDEX('TRANSACTION DETAILS - SAMPLE'!F:F, MATCH(ROW(A1), 'TRANSACTION DETAILS - SAMPLE'!H:H, 0)), "")
For the Credit column:
=IFERROR(INDEX('TRANSACTION DETAILS - SAMPLE'!F:F, MATCH(ROW(A1), 'TRANSACTION DETAILS - SAMPLE'!H:H, 0)), "")