+ Reply to Thread
Results 1 to 13 of 13

HELP A TEACHER!: Use Conditional formatting to insert text

  1. #1
    Registered User
    Join Date
    08-29-2015
    Location
    Ras al Khaimah
    MS-Off Ver
    2013
    Posts
    6

    HELP A TEACHER!: Use Conditional formatting to insert text

    Hi,

    I have a mark book of students tests which is broken down question by question.

    I have created a MAX MIN function which will then highlight their best and worst questions.

    My challenge in this ...

    Can excel pick up on which cells have been highlighted (there are two colours depending on if it is their best or worst grade) and can it then place text depending so that I can then mail merge it.

    PLEASE HELP!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    Hi, welcome to the forum

    You wouldnt use CF for this, all CF does is change the formatting (cosmetics) of a cell, it cannot put text in a cell.

    Try this instead...
    =INDEX($I$2:$P$2,MATCH(V4,$I4:$P4,0))
    copied down and across

    NOTE: this will not take into account any duplicate highs or lows, it will return the 1st match it finds
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-29-2015
    Location
    Ras al Khaimah
    MS-Off Ver
    2013
    Posts
    6

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    You are a scholar and a gentleman!

    This will definitely do the trick!

    Do you know if there is anyway to get it to include the duplicates?

    Chris

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    Do you know if there is anyway to get it to include the duplicates?
    not easily

  5. #5
    Registered User
    Join Date
    08-29-2015
    Location
    Ras al Khaimah
    MS-Off Ver
    2013
    Posts
    6

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    Dammit!

    Oh well if you get the time to figure out a way even if it means sticking stuff in access I would be most grateful for a solution as it would be a life saver next year!

    But what you've done for me now is fantastic!

    Thanks a million!

    Chris

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    Thanks for the feedback

    Would you be OK with duplicates in separate columns?

  7. #7
    Registered User
    Join Date
    08-29-2015
    Location
    Ras al Khaimah
    MS-Off Ver
    2013
    Posts
    6

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    Yeah I wouldn't mind it being in separate columns; that's just as easy to mail merge

    Chris

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    I just noticed that col V (min) is pulling 0's if they are in the row, Im sure that is not what you wanted?

    If it was, then OK, otherwise change V5 to this ARRAY formula, then copy down...
    =MIN(IF('Common assessment 1'!$I5:$P5>0,'Common assessment 1'!$I5:$P5))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    From your sample, it looks like the "max" duplicates for min is 4 and for max is also 4. Is that typical, or could there be more (or less) than 4 for for min and max?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    OK, I think I have it for you...added 3 extra helper columns to the Mi n and Max and used this ARRAY formula instead of the formula above that I gave you...
    X5=IFERROR(INDEX($I$2:$P$2,SMALL(IF($I5:$P5=$V5,COLUMN($I$5:$P$5)-8),COLUMNS($I$5:I5))),"")
    ARRAY entered ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Then copy across 3 more columns, then copy all 4 down

    Repeat this for the MAX (which will now start in AB...
    =IFERROR(INDEX($I$2:$P$2,SMALL(IF($I5:$P5=$W5,COLUMN($I$5:$P$5)-8),COLUMNS($I$5:I5))),"")
    also ARRAY entered
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-29-2015
    Location
    Ras al Khaimah
    MS-Off Ver
    2013
    Posts
    6

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    They are meant to be like that ... this means that they are struggling in numerous areas.

    That about right really ... just looked over my previous results and they are all around 4 ...

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    Quote Originally Posted by cmorrison78515 View Post
    They are meant to be like that ... this means that they are struggling in numerous areas.

    That about right really ... just looked over my previous results and they are all around 4 ...
    OK, then ignore my MIN formula, but my suggestion in post #9 should still work

  12. #12
    Registered User
    Join Date
    08-29-2015
    Location
    Ras al Khaimah
    MS-Off Ver
    2013
    Posts
    6

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    Wow you are a genius!!

    I'd buy you a pint if I could!

    Thanks again!

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: HELP A TEACHER!: Use Conditional formatting to insert text

    haha thank, I would accept - if I could

    (see, told you it wouldnt be easy - well, simple lol) Im happy we got you where you wanted to be

+ 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. Replies: 6
    Last Post: 01-11-2016, 09:04 AM
  2. insert text and a cell value in conditional formatting
    By alexcol in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2014, 07:21 PM
  3. [SOLVED] Auto Text insert based on IF & Conditional Formatting
    By skylinekiller in forum Excel General
    Replies: 16
    Last Post: 04-18-2014, 06:49 AM
  4. Replies: 3
    Last Post: 06-25-2013, 04:41 AM
  5. Insert Text using Conditional Formatting
    By kyandbe24 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2013, 08:00 PM
  6. Teacher - trying to create if rule of % into text mark A B C etc
    By jayendra in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-14-2008, 06:58 PM
  7. Conditional Formatting - if insert a comment
    By rexwrx in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-31-2006, 01:13 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