Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting

    I have Excell 2000. In Cell B11 I have the following text 1234567/A but may be 1234567/B, 1234567/T1, 1234567/T2 or 1234567/T3.

    What I need to do is in cell F11, J11, N11, R11, V11 if B11 = 1234567/A to copy the contest (1234567/A) to all those cells.

    IF B11 = 1234567/B then copy '1234567/B' to cells N11, V11 only

    If B11 = 1234567/T1 then copy '1234567T1' to Cell R11

    If B11 = 1234567/T2 then copy '1234567/T2 to Cell N11

    IF B11 = 1234567/T3 then copy 1234567/T3' to Cell V11

    I will like conditional formatting to accomplish these tasks.

    Thanks a million
    Last edited by ruirib; 2014-06-19 at 19:21. Reason: Moved from Contact Us

  2. #2
    Super Moderator jwitalka's Avatar
    Join Date
    Dec 2009
    Location
    Minnesota
    Posts
    6,797
    Thanks
    117
    Thanked 799 Times in 720 Posts
    Emailed user suggesting registration

    Jerry

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Conditional formatting does not copy, it only sets formatting.

    To copy cells would require a macro. Or it could also be accomplished with a formula in each of those cells. Could you elaborate on what exactly you want to accomplish so we can suggest the best method to do this.

    Steve

  4. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts

    Nested IFs are one solution

    I don't know that you can do it with conditional formatting, but could do it with formulae using a few nested IFs as per the attached file.

    Hope this helps.
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    unclehewie,
    To match the required description ["If B11 = 1234567/T1 then copy '1234567T1' to Cell R11", the output does not have the forward slash in it. ] your formula in R11 should be:

    =IF(B11="1234567/A",B11,IF(B11="1234567/T1","1234567T1",""))

    An alternate formula to the nested IFs (which has limits to the number of them) for N11 and V11 would be to use the OR function. In N11:
    =IF(OR(B11="1234567/A",B11="1234567/B",B11="1234567/T2"),B11,"")

    and in V11:
    =IF(OR(B11="1234567/A",B11="1234567/B",B11="1234567/T3"),B11,"")

    Or if the list is long, you could use MATCH to just look at the array list instead of explicitly listing each comparison. In N11:
    =IF(ISNUMBER(MATCH(B11,{"1234567/A","1234567/B","1234567/T2"},0)),B11,"")

    and in V11:
    =IF(ISNUMBER(MATCH(B11,{"1234567/A","1234567/B","1234567/T3"},0)),B11,"")

    Steve

  6. #6
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    Hi Steve,

    I saw that the slash was missing but assumed it was a typo since all the others had it in... Only gonzalezw can know for sure. I should have mentioned this in my post but was rushed as I was at work.

    As you say, nested IFs have a limit and I believe it's seven in Excel 2000 so this was well inside that limit. Since all the other options come up with the same result, I saw no need to alter the formulae that I worked up, and as I said, I was at work and just trying to help.

    Unclehewie

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I was not criticizing, your result is probably what is really desired, I just wanted to point out it was not the result asked for.

    The variants I proposed were in case the sample was a lot smaller than the actual list. Even before the 7 nested loop limit hit, the nested IFs can get cumbersome, so other options were presented as in the ORs, but with longer lists, the explicit array or even a range list would make more sense.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •