1. ## 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

2. Emailed user suggesting registration

Jerry

3. 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. ## 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.

5. 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. 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. 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
•