query formula

[zuyya]

Hi Guys,

I am new, here and i really need some help.

I have a table with 4 columns. I need to check the last column for values
greater than 0. Then display all of the rows for those cases, and ignore the
cases where the value in the column is 0.

7 5 6 7
1 3 3 4
3 2 1 0
5 6 7 9


so in the above example, it would display everything except; 3 2 1 0,
because 0 is not >0.


I need it to be on a seperate sheet from the one that my tables are on. So
that i can click on it and the 'filtered' data is there ready for print.


I have tried the auto filter function, but that is not what i am looking for.

I have been playing with the following =IF(I1>0,K1,'data'!A4) types of
formula but without success.

Thanks Guys!!!!

 

[hot dogs]

I have 2 suggestions; have the cells in the second data list equal to the
cells in the first data list (or copy the data to your second page), then
select the last column and go to format|cells|custom and enter 0;-0;;@ in the
type box, that will then turn all zero's to blanks. You can then use the auto
filter to filter for non-blanks in the last row, and any row with a zero in
the last column will be removed.

Or, instead of having the cells in the second data list equal to the cells
in the first data list, use the formula =IF($E5>0,B5,"") in the top left
corner of where you want to put the second data list, where E5 is the top
right cell of the first data list and B5 is the top left cell of the first
data list. Then use the fill handle to copy the formula to the right (4
cells, or however many columns there are in the list) and then use the fill
handle again to drag the formula down to however many rows there are in the
list.

 

[zuyya]

Thanks hot dogs, but I need it to be automated, so i dont have to do those
steps. I need to open the sheet, and its all there, just to print.