Snasui Excel Forum

Hi Dear!
Today I come up with an issue to follow up advance amount.
I have three sheets
Advance
Adv. sheet
Clearing sheet
Report sheet
I have input advance amount in sheet advance with log number.
I also enter amount of advance liquidation in sheet clearing with log number and invoice date.
I need some formulas to track advance amount not yet cleared and alert if it is paste due invoice date from 5 days ahead it shows <PD>.
I also attached a template.

Best wishes,

Try this,

Cell A2 on sheet Report fill in with

=IFERROR(INDEX(Adv!A:A,AGGREGATE(15,6,ROW(Adv!A$2:A$100)/ISNA(MATCH(Adv!$B$2:$B$100,clearing!$B$2:$B$100,0))/(Adv!$B$2:$B$100<>""),ROWS(A$2:A2))),"")

Press enter then copy to right and down.

Hi Dear!
Thank you so so much.it works flawlessly