Query/Formula

zuyya

New Member
Joined
Oct 10, 2006
Messages
2
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 all the 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.

col1 col2 col3 col4
1 3 3 4
3 2 1 0



so in the above example, it would only display; 1 3 3 4 because 4>0. In the other example it should totally ignore it. because 3 2 1 0, 0 is not >0.

=IF((I1>0),K1,''data'!A4')

I tried this But I get a total error

can someone help

thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

viquarshaikh

Active Member
Joined
Mar 21, 2006
Messages
444
Hi zuyya
Welcome to the board

It's fairly simple what you're trying to achieve here..

You could try having Autofilter on the data and then filter on the chosen Column for values greater than zero. Menu Option Data--> Filter ---> Autofilter .... you have a host of options there to choose from. Choose greater than from the first box and enter zero in the text box on the right. SElect the entire visible range , copy and paste it in a different location to have only the filtered records pasted.

If that doesn't suit you, you could try your IF formula
Code:
=IF(I1>0,K1,'data'!A4)

Check if this helps
 

zuyya

New Member
Joined
Oct 10, 2006
Messages
2
Thanks for that viquarshaikh

The auto filter method works, however, i am trying to have it be automated, on a seperate sheet. So that sheet only displays the data for the whole row where the last column >0.

The formula only returns a 0 value or a 1 value. I cant get it to use what it finds, i.e value> 0 and to display the who row for the values >0



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

I just want the new sheet to display where the last column does not have 0.

i.e

2 3 4 5 2
1 3 4 5 9

and ignore the rest.

does that make sense?

thanks
 

Forum statistics

Threads
1,141,757
Messages
5,708,355
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top