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

IF Formula - Skip Rows That Dont Meet Criteria

Don Black

New Member
I have been looking for a formula that would only return values where the criteria was met.
Skipping any other cells where the statement was not true. It will also be referencing a different worksheet.


I started out with a Vlookup which did not give me what I needed.

Then I used the "ISERROR" function but still did not get the information I needed.

IF(ISERROR(SMALL('WO-SO'!C2571:C3147,ROW()-2)),"",SMALL('WO-SO'!C2571:C3147,ROW()-2))
 
Hi ,

A formula which does not work correctly cannot give a proper idea of what your requirement is.

A clear and complete explanation can ; so can a sample workbook with enough variety of data.

Narayan
 
Sorry, Narayan

In the worksheet "BackShop MRO", I want to return values for items called "OC" in Shop Area column header. The values must match all of the columns 1 for 1 in both worksheets to avoid any in accuracy later, when sum calculations are needed. I want to reference my data from "Wo-So", only where the cell values for these items meet the criteria, otherwise skipping blanks or any other criteria that does not match.

https://drive.google.com/open?id=0By3hrK3o8_Z4TXhxVWlTY0RRSUE&authuser=0
 
That works how do I remove the blank dates. and can I just drag this formula through the other cells or I need to adjust for the other columns. Thanks
 
Hi ,

What do you want should appear in the rows that have blanks in column F ?

Dragging is not possible since the columns are not the same as in the other tab. You can see that the reference for the INDEX function is being adjusted according to the column in which the information appears in the other tab.

For example , the year appears in column A , the WO # appears in column C , Date Opened appears in column E , and so on.

Narayan
 
This formula does not work now that I think about it when I filter on "Wo-So" , get the proper dates on that worksheet, but on the "Backshop," the dates do not match up.
 
Hi ,

If you can specify your requirements , and upload a working file , we can see what is to be done , otherwise you have to troubleshoot yourself.

Narayan
 
I used this formula, and it worked precisely, only problem I had to filter the blanks where as I wanted to skip the blanks and go to the next value where criteria is met.

=IF('WO-SO'!$I2570="OC",VLOOKUP('WO-SO'!$A2570,'WO-SO'!$A:$AF,1,FALSE),"")


I had link each column index to its header in the WO-SO worksheet. If you can adjust this formula so it skips the blanks or any criteria where the condition is not met then. Please do so thanks again for the help.
 
Hello Narayank991,

I tried to expand that formula by using a date criteria column "C" of Wo-SO worksheet instead of shop area.

I am not sure but the function is not working at all when I do crtl+Shift+enter.

=INDEX('WO-SO'!$A$3:$A$500,SMALL(IF(OR('WO-SO'!$C$3:$C$5000="2014",'WO-SO'!$C$3:$C$5000="2015",'WO-SO'!$C$3:$C$5000="open"),ROW('WO-SO'!$C$3:$C$500)-MIN(ROW('WO-SO'!$C$3:$C$500))+1),ROWS($A$1:$A1)))
 
Hi ,

You cannot use the OR function ; the formula will no longer behave like an array formula even if you enter it using CTRL SHIFT ENTER.

Can you specify what conditions you wish to use ?

Is it that you wish to check whether the range 'WO-SO'!$C$3:$C$5000 contains the text 2014 or the text 2015 or the text open ?

Looking at your file , it is column B which has these values , not column C.

See if this is OK.

Narayan
 

Attachments

  • WO RECORDER - web version1.xlsm
    857.2 KB · Views: 16
I tried to copy the formula to my original workbook and I am getting value errors and #num errors. Why is this happening, if the data is set up the same way as my test workbook?
 
Hi ,

Sorry , my mistake ; if you see the uploaded file , you will find that I have changed the formula in column B ; this is because some of your cells in column F have the text value ERROR ; if the formula is not revised , it gives rise to a #VALUE! error.

You will need to incorporate this change in your workbook.

Narayan
 
Back
Top