+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting with values from multiple cells

  1. #1
    Registered User
    Join Date
    01-20-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    2

    Conditional formatting with values from multiple cells

    Currently I'm creating a database for models and I want some automation in it.
    I know how to do some conditional formatting but not that extensive.

    So what I want to do:
    The models are divided in 2 groups; fashion and commercial. They belong to one group depending on their height.
    And the height requirement is different for each gender.

    So basicly:
    If a model is female and 175 or heigher, I want her length to be green and her group to be Fashion
    If a model is female and below 175, I want her length to be regular and her group to be Commercial
    If a model is male and 184 or heigher, I want his length to be green and his group to be Fashion
    If a model is male and below 184, I want his length to be regular and his group to be Commercial.

    So; I want the cell of the height to change depending on it's own value, which is above 175 for female and 184 for male but also on the value of the gender. So that it wouldn't turn green for males on 175.
    And I want the cell of the group to change the name depending on wether the background of the height is green, if that's even possible.

    Could any of you help me out?
    The colums; B is for the gender, J is for the height and V is the group

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Conditional formatting with values from multiple cells

    It's really hard to fill a cell with a value based on the color of another cell.

    Fortunately you can fill column V just based on the same rules to color with, so you don't even need to go through the color, like this:

    Please Login or Register  to view this content.
    You can use a two-input formula to conditionally format the "length" column to green using "Format values where this formula is true":
    Please Login or Register  to view this content.
    Set that to format the cell with green, and you should be good to go.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional formatting with values from multiple cells

    Group in V
    =IF( OR ( AND(B2="male", J2 > = 184) , AND(B2="female", J2 > = 175) ) , "Fashion", "commercial" )

    and then you want green if the group is fashion
    so you can just use the value in V

    so a conditional formatting rule

    select A2:V100 or whatever you range is

    then in the rule

    =$V2 = "fashion"
    and the whole row will highlight
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    01-20-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    2

    Re: Conditional formatting with values from multiple cells

    Quote Originally Posted by ben_hensel View Post
    It's really hard to fill a cell with a value based on the color of another cell.

    Fortunately you can fill column V just based on the same rules to color with, so you don't even need to go through the color, like this:

    Please Login or Register  to view this content.
    You can use a two-input formula to conditionally format the "length" column to green using "Format values where this formula is true":
    Please Login or Register  to view this content.
    Set that to format the cell with green, and you should be good to go.
    I'm not sure what I'm doing wrong here.
    The first formula I pasted in the V column in the regular formula/text bar.
    The second one I did as a new rule and than "use a formula to determine which cells to format" in the L column.
    But in both cases it tells me there's a problem with the formula.

+ 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] Conditional Formatting based on Multiple Values of other Cells
    By Sovrin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2016, 08:10 PM
  2. [SOLVED] Conditional formatting for cells containing multiple values.
    By chaitreya in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-24-2015, 03:40 PM
  3. Conditional formatting, based ot multiple values in 2 cells.
    By kaosbg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2014, 02:47 PM
  4. Replies: 4
    Last Post: 07-25-2014, 05:17 AM
  5. [SOLVED] Conditional Formatting Using Multiple Values in Other Data Validation Cells
    By Uclaid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2013, 06:47 PM
  6. Replies: 2
    Last Post: 08-27-2011, 03:05 PM
  7. Replies: 1
    Last Post: 08-20-2009, 10:58 AM

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