+ Reply to Thread
Results 1 to 3 of 3

Multiple nested IF statements and AND statements

  1. #1
    Registered User
    Join Date
    12-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    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.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Multiple nested IF statements and AND statements

    Hi TonyGetz
    a good place to start would be with one of jacks data base freebies
    http://www.excel-it.com/vba_examples.htm
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    12-07-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multiple nested IF statements and AND statements

    Hi Pike, thanks for that response. jacks website isnt loading for me. Also, I dont have any programming experience so VBA scripts etc are above my head.

    Does anyone have any advice about any excel specific formulas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1