+ Reply to Thread
Results 1 to 3 of 3

Assigning a value to a cell based on conditions of two other cells being equal

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    athens, ga
    MS-Off Ver
    Excel 2003
    Posts
    2

    Assigning a value to a cell based on conditions of two other cells being equal

    Okay I have tried searching and I am just not smart enough to figure out all this programming stuff so I am not exactly sure what to do, but I assume it is simple.

    I have three columns, one is Purchase Size, one is Count Size, and one is Purchase Count.

    I just need a formula that will look at the Purchase Size and Count Size and if they are equal automatically force the Purchase Count to be a value of 1. If they are not equal I need it to allow the user to input the correct amount.

    Both the Purchase Size and Count Size columns utilize a list of possible selections.

    It would be nice to also lock the ability to alter the 1 if the two columns are identical

    Thank you very much
    Last edited by chrisworrell; 01-11-2011 at 04:40 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,618

    Re: Assigning a value to a cell based on conditions of two other cells being equal

    There are two ways to tackle this. The simplest one, using Excel formulas, may not meet your need to control the data. If the columns are A, B, and C, you can put the following in, say, C2:

    =IF(A2=B2,1,IF(D2="Enter Value Here","",D2))

    And put the following in D2, currently not used:

    =IF(A2=B2,"","Enter Value Here")

    Then you unlock the cells in column D and turn on worksheet protection. The user will not be able to change the value in C2. If the user enters something in D2, then use conditional formatting in D2 so that if C2=D2, the font in D2 is white and effectively invisible.

    A simple example is attached.

    I think this solves your problem with formulas only. However, it has the drawback that once the user has entered a number, the formula is replaced by the value and you can't go back to the prompt, if some data changes. Also, you have to make sure that the formula in Column D is copied down to enough rows to accommodate new data (in my example I have filled it down several rows to start with).

    A more sophisticated solution could be developed by writing VBA that would prompt the user to enter a value only when it's needed, and not need to use an extra column to do it.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 01-11-2011 at 04:42 PM. Reason: updated text to refect update in workbook
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    01-11-2011
    Location
    athens, ga
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Assigning a value to a cell based on conditions of two other cells being equal

    yea I was afraid it would require VBA, im developing this sheet to be completely idiot proof so our customers can't screw something up, because they like to be able to say Case-Case-12. and they dont realize there cant be 12 cases in a case.

    thanks for your quick response

+ 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