If Yes then only show these items in new table or named range

[RussB]

I am trying to get excel to list only the parts actually used if the value in a cell = yes and that it also has a quantity of 1 or more in the other cell. If there is not Yes value or Quantity I don't want it to be shown in the actual parts being used for certain jobs. I've attached how I would like the spreadsheet to look, but I'm not sure exactly how to get it to work.

PARTS.xlsx

any help would be much appreciated.

Thanks

[Pete_UK]

I inserted a new column E and put this formula in E3:

=IF(OR(C3="no",D3=0),"",MAX(E$2:E2)+1)

This was copied down automatically.

Then in G3 I used this formula:

=IF(ROWS($1:1)>MAX(E:E),"",IFERROR(INDEX(B:B,MATCH(ROWS($1:1),E:E,0)),""))

with this formula in H3:

=IF(ROWS($1:1)>MAX(E:E),"",IFERROR(INDEX(D:D,MATCH(ROWS($1:1),E:E,0)),""))

These two formulae can be copied down as far as you need them.

Hope this helps.

Pete

[RussB]

Thank you so much!!

[Pete_UK]

You're welcome.

I see you have marked the thread as SOLVED - you might also like to click on the "star" icon at the bottom of my post to add to my reputation (this also applies to other posts that you find helpful - not only on this thread).

Pete

[RussB]

Sure thing Pete. Thanks again for all of your help.

[ROHIT ADLAKHA]

Hello Pete,

Can you please solve this in attached sheet by Russ.

Tnx in advance