+ Reply to Thread
Results 1 to 6 of 6

Choose from a column dependent upon a value

  1. #1
    Registered User
    Join Date
    09-01-2012
    Location
    Staffordshire, England
    MS-Off Ver
    O365
    Posts
    129

    Choose from a column dependent upon a value

    Please see attached. I want the formula in G2 to say: IF the value in G11 is less than 250, then display "Below min order value", IF between 250-499, return adjacent value in column B, IF between 500-999, return column C, IF between 1000-1999, return column D, IF above 2000, return column E.
    Book3.xlsx

  2. #2
    Registered User
    Join Date
    08-05-2013
    Location
    USA
    MS-Off Ver
    2010
    Posts
    76

    Re: Choose from a column dependent upon a value

    You can put a nested If statement in H2: =IF($G$11<250,"Below min order value",IF($G$11<500,B2,IF($G$11<1000,C2,IF($G$11<2000,D2,E2))))

  3. #3
    Registered User
    Join Date
    09-01-2012
    Location
    Staffordshire, England
    MS-Off Ver
    O365
    Posts
    129

    Re: Choose from a column dependent upon a value

    The problem is that because C11 is summing column C, it is creating a circular reference. Any ideas on how to solve this?
    Book4.xlsx

  4. #4
    Registered User
    Join Date
    09-01-2012
    Location
    Staffordshire, England
    MS-Off Ver
    O365
    Posts
    129

    Re: Choose from a column dependent upon a value

    Sorry, I mean G11 and column G.

  5. #5
    Registered User
    Join Date
    08-05-2013
    Location
    USA
    MS-Off Ver
    2010
    Posts
    76

    Re: Choose from a column dependent upon a value

    That's why I suggested to put the formula in column H, not G. There is a way to allow circular references, but you need the iterations defined. File>Options>Formulas>Enable iterative calculation. However, that won't solve this problem as the total for G11 will always be 0 because G11 just sums everything above it, which the line item totals in your first workbook post would be over-written by the formula I wrote if put in G.

    So I think the best bet would be to put the formula in H, or if you want to keep it in G, you need to change the total order value formula to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and put this formula in G2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-01-2012
    Location
    Staffordshire, England
    MS-Off Ver
    O365
    Posts
    129

    Re: Choose from a column dependent upon a value

    Sorry Jeff, I haven't managed to do this. I will put it in the Commercial Services section and you can complete the form there for me if you wish. Many thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy Data in column B to other columns dependent on Contents of column A
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-07-2013, 05:54 AM
  2. Replies: 1
    Last Post: 07-29-2013, 07:35 PM
  3. Replies: 10
    Last Post: 01-17-2013, 01:42 PM
  4. Change column reference to next column dependent on date
    By dinomike20 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2011, 07:20 PM
  5. Replies: 6
    Last Post: 11-28-2010, 07:52 PM

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