skip nav

Forums

Scott's tips

Sun, Dec 1 2019 1:13 AM by ScottHope. 285 replies.
  • ScottHope United Kingdom
    6,221 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 United Kingdom
    6,221 Posts
    Thu, Jan 19 2017 11:10 AM
    Posting an Excel or Word table.
    USERNAME TITLE TIER LEVEL AVERAGE ROUNDS EARNINGS CONTRIBUTIONS LAST ROUND
     Akasz000 Recruit Hack 5 150.00 0 $0.00 0 27/10/2017
     Alesiaa Recruit Hack 15 150.00 0 $0.00 493 20/07/2017
     AnthonyEmerson Recruit Hack 12 150.00 0 $0.00 0 07/08/2017
     avantxD Recruit Hack 4 150.00 0 $0.00 0 07/06/2017
     Bobby2583112 Recruit Amateur 33 150.00 0 $0.50 244 09/07/2017
     bola Recruit Amateur 21 150.00 1 $0.50 0 17/04/2017
     Brian6176 Recruit Pro 45 150.00 0 $1.50 5,345 05/09/2017
     Adelious Recruit Hack 15 124.00 1 $0.00 10 04/06/2017
     Amir0340 Recruit Pro 37 93.25 38 $1.50 6,435 24/09/2017
     amol134 Recruit Pro 33 91.00 10 $1.50 3,292 09/12/2016

    Instructions :
    (This may not work in Internet Explorer.)

    1. In Excel or Word, save your document as a webpage.
    2. Go to the saved file, double click it and it should open in your browser.
    3. Start a new message at WGT.
    4. Click anywhere in the table that is open in your browser and then press CTRL & A on your keyboard to select the whole table.
    5. IF YOU HAVE NO WGT TOOLBAR, you will have to drag your table into the standalone WYSIWYG editor.
    6. With the whole table selected as in IV, left click and hold down the mouse button anywhere on the table, and then drag it into your WGT message or the WYSIWYG editor.
    7. If your table is in the WYSIWYG editor, click the HTML button and then copy everything in the HTML source editor. Then paste that into your WGT message.
    8. If you now post your WGT message, you should be looking at your table. If it is too wide (as mine is) and you can't see it all, send me a message and I'll tell you how to modify the HTML to add in a horizontal scrollbar.
    9. Thank you.
  • msulaman United States
    423 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 United States
    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 United Kingdom
    6,221 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 United Kingdom
    7,868 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 United Kingdom
    6,221 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 United States
    423 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 United Kingdom
    7,868 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 United States
    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