?x Help (Vlookup, Index, IF) ?

I have a workbook that has 4 sheets. 1 sheet is "inventory" and others are Purchase Orders. Sheet 1 has different vendors I would like a formula that will look on Sheet 1 for Vendor ABC and if it finds an entry for ABC and if a part needs order based on if the column "Order Amount" (which is conditional formatted to turn red when part needs ordered) is less that Minimum Inventory if this criteria is meet it takes that info needing ordered and fill in the PO tab (sheet2).

Its very difficult to try to explain how I would like for it to do. I prefer not a macro or VB code. I prefer a formula but would like the formula on sheet 1 as the PO tab will be emailed to the vendor. One other thing be nice to have the formula not in any cell where data will be entered in Sheet 1. I can have a formula for each vendor and each row on sheet 1.

Anything like this possible? I think I could do a formula in the PO(sheet 2,3,4) to fill in from criteria from Sheet 1 but this will be emailed and possible quantity ordered may be edited, all others would not change on Sheet 2.

I have attached the workbook for possible help in seeing what I want.
Thanks in advance!!!!

Not possible I'm afraid. You'd have to have formula in the PO sheets to allow population of the data. If you were completely against having this then you'd need to complete using VB. Let me know which of the two you'd prefer and I'll put something together for you.

Sorry for the delay with this one I've been busy. I've got a solution for you.

Using a count with absolute reference we can label the instances then use a look up and match to pull just these off in the PO sheet. I've attached a completed one for only one PO but just replicate the code onto the other PO Sheets. I've used the numbers on the left of the PO's to pull out the instances within the main sheet. You can hide the two index columns and it will still work fine.

Hope this helps.

Any questions then please let me know.

That works unbelievably GREAT! I did one complete PO tab and it works great even when sorting and filtering. Thanks for the help. I thank you for the time u spent on this and hope that it was not much like work and was somewhat enjoyable. I have 5 PO tabs to create and once again thanks for the help and time you spent on this.

You're more than welcome. Always happy to help