Need to create a list on a separate sheet based on an if statment.

[lunbek90]

I need to create a list of drawing numbers based on a criteria that i have already created. My spreadsheet is based on drawing that have a bill of materials and i need those drawings to be populated on a separate sheet in a new column.

My sheet has about 20 columns but only 2 are relevant. The first column has the drawing number. The other relevant column i have written an if statement that determines if the drawing has a bill of materials associated with it the if statement just returns a 1 or a 0 in that column.

On a separate sheet i need to go thru all of the data and if the column with the if statement has a 1 then i need to copy that drawing number into the list.

I think it has a simple solution but i am just not connecting the dots.

[Pete_UK]

I appreciate that you have just extracted the data to give us a simplified file, but it would help if you can give us the actual IF formulae that you use to establish the 1's and 0's that are currently shown in columns E to H. Rather than have another set of helper columns for these 4, you could amend those IF formulae to return a unique sequential number, and then it would be quite simple to extract the desired data into the other sheet. I imagine that you have a formula like this:

=IF(AND(BOM<>"",Level=1),1,0)

where BOM is the Bill of Materials column and Level is shown as column B. Instead, you could change this to:

=IF(AND(BOM<>"",Level=1),MAX(X$1:X1)+1,"-")

assuming the formula is in X2. This will give you a series of sequential numbers (and hyphens, where the conditions are not met), and then you can use and INDEX/MATCH/ROWS formula in the other sheet.

Hope this helps.

Pete

[lunbek90]

Pete,

The if statement does this. =IF($B5>$B4,IF($B5=$B4,0,IF(LEFT(TRIM($C5),1)="x",0,1)),0) The X denotes that it is made from raw material and i don't need those prints for what i am doing so i want to cut those drawings out.

The if statement is working fine just doing the 1 and 0 part. It may not be very efficient, I am definitely not a programmer, but it works. I was just wondering how i take those pass the IF statement to make me a list for printing out drawings.

Thanks for the help so far.

[Pete_UK]

I'm not sure how that formula works. You are basically saying IF B5>B4 THEN do_something OTHERWISE return zero, which is basically the same as IF (B5=B4 OR B5<B4) THEN return zero OTHERWISE do_something.

Your do_something term then checks to see if B5 equals B4, but it can never be satisfied, so that particular IF is redundant. Perhaps you mean:

=IF(AND($B5>=$B4,LEFT(TRIM($C5),1)="x"),0,1)

Anyway, to apply my method to your file as submitted, you can put this formula in J2 (with the headings in J1:M1) :

=IF(E2=1,MAX(J$1:J1)+1,"-")

Copy this into K2:M2, then copy the 4 formulae down as far as you like. Then, with the headings in A1:D1 of Sheet2, you can use this formula in A2:

=IFERROR(INDEX(Sheet1!$C:$C,MATCH(ROWS(Sheet1!$1:1),Sheet1!J:J,0)),"")

Copy this across to B2:D2, then copy the 4 formulae down as far as you like.

What I was suggesting earlier, is that instead of your formula just returning either 1 or 0, you could have it returning MAX(first_cell:current_cell)+1 (depending on which column it is in), or "-" (although you could still use zero if you wish). Then you wouldn't have to use the 4 extra columns J to M.

Hope this helps.

Pete

[lunbek90]

Pete,

It is working and doing what i need it to do. It's definitely not the most efficient way but it will get the job done for now. I am working on some other stuff that involves the level breakdown and that is why i wasn't just doing it all in a single cell as you talked about at the end. I was just having a mental block on the index part. I have done a list like this before i was just not making the connection.

Thanks

[Pete_UK]

You're welcome - glad you got something working in the end.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum (in terms of posts), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete