Dynamic Range

[vanbasten007]

Hi guys,

I am working with a dynamic range. I am hoping you can suggest a solution. Here is the problem (see the attached file). I have two sheets. Summary by project is a table that show financial performance by project. However, there are several hundred possible projects and only a few will be relevant to a particular group and every period, there may or may not be expenses associated with additional projects are part of the list. So right now, the rightmost column in Summary by Project simply uses the formula =IFERROR(INDEX(MyRangeOne,ROW()-6),"") to refer to all the possible projects in worksheet Projects. Obviously if I have lines for all 300 projects, the report would look really ugly and I would have to hide the empty lines. However if a project that previously did not have an expense now has an expense, it would be hidden. So is there a way to automatically hide projects that don't have any data in columns D-R or expend only those lines that have data in columns D-R? The actual amounts would simply be a sumifs function based on monthly updated worksheet that the file will include.

Thanks, your help is much appreciated.

[FDibbins]

1 way to do this would be to use a helper column and then apply filters...Home tab/Editing/Sort and Filter, Filter

1st put this in R7, copied down...
=COUNTIFS(C8:Q8,"<>0",C8:Q8,"<>")
Then apply filters and filter out all 0's

[vanbasten007]

Thanks much, it works. How come I have to use two criteria in the formula?

[FDibbins]

If you leave out the last criteria, it counts empty cells