Forum Discussion

LionKing9450's avatar
LionKing9450
Copper Contributor
Dec 26, 2024

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 <>?

  • LionKing9450's avatar
    LionKing9450
    Copper 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)), "")

     

Resources