• 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.

Excel formula

deenoseban

New Member
Hi All,

I need a small help in fixing my formula.
i have a report from erp with few employees name and the amount they owe. It is a dynamic report and changes everyday.
My requirement is that I need a formula to link with employees who has a balance

In the below screenshot column D shows the balance and i wrote a formula to bring the values =IF($D2>0,B2,"")
My objective is avoid blanks with out using filter. Is there a dynamic formula to fix this.
72063
 

Attachments

  • Excel file.xlsx
    9.5 KB · Views: 8
or for older versions,
maybe use a combo index/aggregate.

[G1] (helper cell): =COUNTIF(D:D,">0")
[I2]=IF((ROW()-1)>$G$1,"",INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-1)/($D$2:$D$6>0),ROW()-1)))
 

Attachments

  • Chandoo_FilterRange Excel file.xlsx
    10.4 KB · Views: 5
Back
Top