Forum Discussion

Rizwan_Hussain's avatar
Rizwan_Hussain
Copper Contributor
Apr 29, 2022
Solved

Need formula to track unpaid bills

Hi, is there any formula in excel or technique to place bills existing in another sheet to place under receipt and remaining bills under the 2nd receipt and so on.
excel
  • mtarler's avatar
    mtarler
    May 16, 2022

    Rizwan_Hussain   Ah, you must have an older version of Excel that doesn't recognize XLOOKUP.  I have changed it to use INDEX( MATCH () ) combo in the attached.

Rizwan_Hussain's avatar
Rizwan_Hussain
Copper Contributor
May 15, 2022

mtarler Thank you so much, 

Facing issue in applying the "=_xlfn.XLOOKUP(SUM(Table2[[#Headers],[Amount]]:[@Amount])-[@Amount]+0.1,receipts[cumulative],receipts[unique ID],,1)" formula in billing sheet as attached below.

Can you please guide.

Rizwan_Hussain's avatar
Rizwan_Hussain
Copper Contributor
May 16, 2022

mtarler, when sheet is opened Xlookup formula in billing sheet turned into #name error. =_xlfn.XLOOKUP(SUM(Table2[[#Headers],[Amount]]:[@Amount])-[@Amount]+0.1,receipts[cumulative],receipts[unique ID],,1)

How can I solve this?

 

Thanks for the support.

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 16, 2022

    Rizwan_Hussain   Ah, you must have an older version of Excel that doesn't recognize XLOOKUP.  I have changed it to use INDEX( MATCH () ) combo in the attached.