Forums

Help › Forums

Re: Scott's tips.

Wed, Feb 21 2024 6:30 AM (745 replies)
  • ScottHope
    9,981 Posts
    Wed, Jan 18 2017 12:52 AM

    ChromeSlayer:
    Thanks for explaining i can change them blue if decide to one day

    Not just blue, any colour name you can think of (provided it's one of these).

    ChromeSlayer:
    Will these new steps u shared with still work for that?

    Yes.

    ChromeSlayer:
    Can u change the color of the subject/header on the blog spot on profile pages?

    It can be done, but you will need to install at least one browser plugin, have some javascript knowledge, and even then no one, other than you, would see it.
  • ScottHope
    9,981 Posts
    Thu, Jan 19 2017 11:10 AM

    Deleted.

  • msulaman
    446 Posts
    Wed, Jan 25 2017 3:34 PM

    Hello Scott, thanks so much for all your contributions to this forum, much appreciated. 
         Is there a way to do conditional formatting in Excel so that the appearance of one cell (or a group of cells) depends on the value of another cell not in this group?
         In other words can I say if the value of cell B2 is negative, then make cells C3 to C6 blue, or something like that. 

    Next question: Can I do conditional math with Excel? Like if B2 is greater than 5, then use one calculation for cell C3 and if the value is less than 5, use another calculation? As far as I know there is no way to do this.

    Thanks in advance for your help.

  • jessek12
    102 Posts
    Wed, Jan 25 2017 7:15 PM

    msulaman:

    Hello Scott, thanks so much for all your contributions to this forum, much appreciated. 
         Is there a way to do conditional formatting in Excel so that the appearance of one cell (or a group of cells) depends on the value of another cell not in this group?
         In other words can I say if the value of cell B2 is negative, then make cells C3 to C6 blue, or something like that. 

    Next question: Can I do conditional math with Excel? Like if B2 is greater than 5, then use one calculation for cell C3 and if the value is less than 5, use another calculation? As far as I know there is no way to do this.

    Thanks in advance for your help.

    Yes to both.

    #1 - Highlight the cells you want to be formatted. Under the Conditional Formatting menu, you'll see "New Formatting Rule". In that menu, the last option is "Use a formula". Just create the formula, in your case "B2<0", and set the format. Should work. You may need an absolute reference to the cell, so use $B$2 in your example.

    #2 - Not conditional math like the example above, but using a formula, specifically an IF statement. For the example you gave: Click in cell C3 and type: =IF(b2>5,(calculation when true),(calculation when false))

  • ScottHope
    9,981 Posts
    Thu, Jan 26 2017 2:15 AM

    Thanks msulaman.

    Unfortunately conditional formatting is not something that I'm terribly good at, but I've had a play around with it whilst watching a few of Mike Girvin's videos (most of which go over my head, but that says more about me than him).

    I've made a quick video to show what I've done, but it only targets your first question.

    Could the conditional maths one be solved using an if function?

    VIDEO.  (Video won't post due to WGT's flawed embedding script).

    ⇧ EDIT : Thanks Jesse. ; ) ⇧

  • WigerToods2010
    8,445 Posts
    Thu, Jan 26 2017 12:55 PM

    jessek12:

    msulaman:

    Hello Scott, thanks so much for all your contributions to this forum, much appreciated. 
         Is there a way to do conditional formatting in Excel so that the appearance of one cell (or a group of cells) depends on the value of another cell not in this group?
         In other words can I say if the value of cell B2 is negative, then make cells C3 to C6 blue, or something like that. 

    Next question: Can I do conditional math with Excel? Like if B2 is greater than 5, then use one calculation for cell C3 and if the value is less than 5, use another calculation? As far as I know there is no way to do this.

    Thanks in advance for your help.

    Yes to both.

    #1 - Highlight the cells you want to be formatted. Under the Conditional Formatting menu, you'll see "New Formatting Rule". In that menu, the last option is "Use a formula". Just create the formula, in your case "B2<0", and set the format. Should work. You may need an absolute reference to the cell, so use $B$2 in your example.

    #2 - Not conditional math like the example above, but using a formula, specifically an IF statement. For the example you gave: Click in cell C3 and type: =IF(b2>5,(calculation when true),(calculation when false))

    Unsure if I'm reading query #2 correctly but I'd just like to add that the IF function can be used on numerous occasions within the same cell.

    e.g.

    Awarding the correct points to a specific team's total in a football (soccer) match.

    3 pts for a win. 1 point for a draw. 0 points for a loss

    Cell A1 (home team)  Cell B1 (away team)

    =IF(A1>B1,3,IF(A1=B1,1,0))

    In the above if Cell A1 is greater than Cell B1 then 3 points are awarded.

     If Cell A1 is not greater than Cell B1 then the 2nd IF 'kicks in'.

     

     

  • ScottHope
    9,981 Posts
    Thu, Jan 26 2017 1:20 PM

    Ah.....nested IF's. I can feel a dizzy headache coming on, lol.

    Thanks for the info Johnny.  ; )

    ⇩ EDIT : Thank you mm, happy to hear you got to where you wanted to go. : ) ⇩

  • msulaman
    446 Posts
    Thu, Jan 26 2017 1:43 PM

    Thanks so much for your answers gentlemen. I didn't know about using a rule in the formatting conditions, Jessek. Great video Scott - showing exactly what I asked about in first question in a clear, concise way. Thanks Wiger for the illustration of a nested if statement. I wouldn't have thought of that.

    I ended up using the if statement to accomplish what I wanted to do and learned a lot in the process too. Thanks again to all.

  • WigerToods2010
    8,445 Posts
    Thu, Jan 26 2017 2:02 PM

    ScottHope:

    Ah.....nested IF's. I can feel a dizzy headache coming on, lol.

    Thanks for the info Johnny.  ; )

     

    msulaman:

    Thanks Wiger for the illustration of a nested if statement. I wouldn't have thought of that.

    I ended up using the if statement to accomplish what I wanted to do and learned a lot in the process too. Thanks again to all.

    You're both very welcome.

    Sometimes ye olde ancient ways are the easiest.

    Regards,

    Wigersauraus.

  • jessek12
    102 Posts
    Fri, Jan 27 2017 6:28 AM

    msulaman:

    Thanks so much for your answers gentlemen. I didn't know about using a rule in the formatting conditions, Jessek. Great video Scott - showing exactly what I asked about in first question in a clear, concise way. Thanks Wiger for the illustration of a nested if statement. I wouldn't have thought of that.

    I ended up using the if statement to accomplish what I wanted to do and learned a lot in the process too. Thanks again to all.

    Not a problem, nested IF statements are extremely useful but it can get confusing keeping all the functions and results straight once you get past 4 or 5. If that's the case, I would recommend using a VLOOKUP.....i could talk Excel all day though.

    The formula-driven conditional formatting can be tricky, sometimes Excel likes to move the cell references on you, but its nice when it works. I just wish that you could use the default color schemes (dark red font, red cell shade, etc.) with it. You have to recreate them on your own. 

    Happy to (try to) answer any Excel questions in the future.

RSS