Forum Discussion
Need formula to track unpaid bills
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 I don't see any example of 'cash / Visa' in any of the files. If those codes mean that to you, fine but I don't have a clue. I used the column under Receipts called Receipt Number because it looked like a unique ID but if that isn't the case then just add another column that is a unique ID (e.g. =ROW() would work)
As for your "desired sheet" it is both a pain to do and not particularly useful for any further calculations or analysis. I suspect this is purely for printing a report for someone who is used to seeing it this way. In the attached I added this 'unique id' column as noted, joined the tables and created a pivot table using the combined data into a format at least very close to your 'desired output'.
I really wish I could better understand what you are actually trying to do/accomplish because I really think you could have a better solution.
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.
- Thanks mtarler, It worked succesfully.
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.
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.