Only Display Rows with Positive Values in Column C

[Dave K]

I am trying to display data (in Sheet 2) based on what has been
entered in a seperate spreadsheet (i.e., in Sheet 1).

If there is a positive value in column C (in Sheet 1), then i want to
show the contents of that entire row (in Sheet 2). If there is not a
positive value in column C (in Sheet 1), then I want that row to be
hidden or not show up at all (in Sheet 2).

Is there a formula or technique that can be employed to accomplish
this "report" view.

Any suggestions would be appreciated.

Thanks!

 

[Gord Dibben]

Copy sheet1...........name it sheet2

Use Data>Filter>Autofilter on sheet2 and filter for negatives in C

Otherwise, you need VBA to copy over just sheet1 C negatives and their row
to sheet2

OR.............just do the filtering on sheet1 and forget sheet2

Check out help on how to create custom views so's you can switch from one
view to another.


Gord Dibben MS Excel MVP

 

[Steve Dunn]

Hi Dave,

Sorry this is a bit late (I don't normally frequent this group, but I'm
having a very slow day at work!)

Put this in the top left cell of your report range:

=INDEX(Sheet1!$A$1:$F$50,SMALL(INDEX((Sheet1!$C$1:$C$50>=0)*
(ROW(Sheet1!$A$1:$F$50)-CELL("ROW",Sheet1!$A$1:$F$50)+1),),
COUNTIF(Sheet1!$C$1:$C$50,"<0")+ROW(1:1)),COLUMN(A:A))

then copy it across and down as required.

You may need to change the comparison operator depending on how you want to
handle 0s; the formula above treats 0s the same as positive values, to treat
it the same as negative values try:

=INDEX(Sheet1!$A$1:$F$50,SMALL(INDEX((Sheet1!$C$1:$C$50>0)*
(ROW(Sheet1!$A$1:$F$50)-CELL("ROW",Sheet1!$A$1:$F$50)+1),),
COUNTIF(Sheet1!$C$1:$C$50,"<=0")+ROW(1:1)),COLUMN(A:A))


HTH
Steve D.