Multiple nested IF statements and AND statements

Hi there,

This is a tricky one. Perhaps it can't be achieved in excel. Any help or advice would be appreciated. Even a suggestion about a better way to achieve this goal. Here goes:

1) We need to check if stock has been booked against an order before the order can be processed for shipping.

2) If the physical stock has arrived we place a "Yes" in column D (Stock IN?) in the row that contains the product.

3) Each product within an order has its own row and an order may contain just 1 row or multiple rows depending on the size of the order. There is no limit to the number of rows per order but the maximum would be around 10 rows. Most orders average 2 to 4 rows.

4) We need to insert a formula into Column B (Dispatched) that checks if there is a "Yes" in column D for the same row. The formula then needs to check if there are other rows in the order so it checks the rows above (and below) and tries to match if the order number in column G (OrderNumber) is the same for the current row.

5) If the order number above (or below) matches (the match could extend for more than 2 rows above or below the current row) then the formula needs to check if a "Yes" appears in column D for the matching row.

6) For all the order number rows that match in Column G (OrderNumber) and contain a "Yes" in column D then the formula must return a value "Ready to ship".

7) If there is a match in the rows above and below column G (order number) however a "Yes" value is NOT FOUND in column D (for any row within the order) then the formula must return a value "Waiting for stock".

Thanks and regards.