• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Index and Match formula

G

gmfston

New Member
Hello Ninjas,

I need help with a formula that will populate values from the Balance Sheet tab to the Trial Balance tab referencing the general ledger account numbers. The issue is that the account numbers in the balance sheet tab vary in length, are listed in different columns, and the accounts highlighted in yellow need to be populated in the trial balance tab as negative numbers so that the trial balance nets to zero. Thank you in advance for your help. Best, Gina
 

Attachments

  • Chandoo 01.10.18.xlsx
    21.3 KB · Views: 8
Hello Ninjas,

Attached is the file where I as able to insert the formula to populate the correct amounts from the balance sheet tab to the trial balance tab. I just need to add a condition that converts the amounts highlighted in yellow (liabilities and equity) on the balance sheet tab to the trial balance (1st) tab as a negative amount or credit. The attachment illustrates my expected results. Any help to add this condition to the attached formula is greatly appreciated!
 

Attachments

  • Chandoo 01.10.18.xlsx
    23.3 KB · Views: 16
In D10, copied down :

=IFERROR(OFFSET('Balance Sheet'!$G$4,AGGREGATE(14,6,ROW('Balance Sheet'!$C$1:$C$90)/ISNUMBER(SEARCH("#"&B10&" · ","#"&'Balance Sheet'!$C$5:$F$94)),1),)*IF(LEFT(B10)>"1",-1,1),"")

Regards
Bosco
 
You must log in or register to reply here.
Back
Top