Solved

need help writing a complex cell condition formula

Posted on 2015-01-09
13
98 Views
Last Modified: 2015-01-23
See the attached doc.  I need to format the sheet to do the following.

If a cell in column F says "Online", then highlight the email a certain color.
If an email is highlighted in that certain color, highlight any other email that is the same, the same color.
test.xlsx
0
Comment
Question by:Shaye Larsen
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40541352
Do you accept a VBA solution ?
gowflow
0
 
LVL 1

Author Comment

by:Shaye Larsen
ID: 40541397
If you can provide instructions for implementing.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40541400
Here it is thru vba but it is not perfect !!!

color any email and press enter then select again this email and it will color all of the same emails with that color. now remove the color and it will remove for the rest as well.

If you accept a VBA then I will try to see how to get the macro to kick on color change as I know there is no event that kicks you need to either change the cell or move back and forth to it.

gowlfow
test.xlsm
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40541434
Here is a solution with conditional formatting.
It use a helper column K, setting a sequential number from 1 up, for each different mail where column F has Online at least once.
The formula for K2 is
=IF(COUNTIFS($C$2:$C$100,C2,$F$2:$F$100,"Online")>0,IF(COUNTIF($C$1:C1,C2)=0,MAX($K$1:K1)+1,INDEX($K$1:K1,MATCH(C2,$C$1:C1,0),1)),0)

Open in new window

Then the calculated number is used to set the conditional format colour.
The conditional formula looks for the number in column K.
The attached sheet is made up to row 100.
Insert rows if you want more, then the formulas will adjust.
Then copy the formula to the inserted rows.
I have made it to handle 5 conditional colours.
If more is needed, extra conditions must be made.
Colour-identical-emails.xlsx
0
 
LVL 1

Author Comment

by:Shaye Larsen
ID: 40541443
I'm not sure how to follow your instructions.

I tried coloring (font color?) an email (the text in the cell) and then hit enter, then clicked on the same and nothing happens.

Again, I can accept VBA if you can provide instructions of how to implement.  I've never done a VBA.

Thanks
0
 
LVL 1

Author Comment

by:Shaye Larsen
ID: 40541462
Ejgil Hedegaard, I loaded your sheet.  Thank but it doesn't meet my requirement.  I basically need any row that has "Online" in the "F" column to highlight the email in that row BLUE, and if that same email is on another row, highlight it the same blue.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40541748

I tried coloring (font color?) an email (the text in the cell) and then hit enter, then clicked on the same and nothing happens.

 Again, I can accept VBA if you can provide instructions of how to implement.  I've never done a VBA.

1) Load the file I posted
2) Once loaded you will have abutton saying Options next to the Address bar on top of the columns letters
3) Press on it and Say Enable
4) Macro are now activated. click on a cell that have an email and color it then press enter the cursor will move to the cell below it and will color the previous cell in the color chosen.
5) press on the arrow up to move back to the previous cell and all the same emails will be colored to the same color.

Is that what you want ?
You can try removing the color from that email it will remove it also from all the similar emails.

If this is what you want and all is ok like this then I can give you steps to implemented in your production workbook.

gowflow
0
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 100 total points
ID: 40541903
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 200 total points
ID: 40541904
I basically need any row that has "Online" in the "F" column to highlight the email in that row BLUE, and if that same email is on another row, highlight it the same blue.

You can do that with a relatively simple formula in conditional formatting

I selected column C and then I used this formula in conditional formatting

=COUNTIFS(C:C,C1,F:F,"Online")

I then applied blue fill format

See attached

regards, barry
CF-test-barry.xlsx
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 200 total points
ID: 40543267
Here is the solution that fixes both the background and the font, as I just realized that you had tried the change the font and nothing happened obviously becoz the solution was applied only on the background.

This solution is much simpler for you as you do the following no back and forth with re-selecting the cell as found a flop in this approach especially when similar cells follows if you change the first one it will not trigger a change color.

now you choose the email that you want, color the background or the font and then once done simply DOUBLECLIKC on that cell and it will change all similar emails to whatever you colored.

PS @Barry, problem is that asker clearly mentioned 'certain color' and not a specific color which means that any emaily could be colored differently therefore setting it to only blue won't help unless we mis-understood the post !

gowflow
test-V01.xlsm
0
 
LVL 1

Accepted Solution

by:
Shaye Larsen earned 0 total points
ID: 40557038
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40558814
Hello the hero,

It would have been good if you could have responded to the suggestions provided - 3 experts have put forward 3 different solutions but you haven't given any feedback - giving constructive feedback would have allowed one or all of us (or another expert) to modify the "inadequate answers" provided, and possibly imbue them with a modicum of adequacy.

regards, barry
0
 
LVL 1

Author Closing Comment

by:Shaye Larsen
ID: 40566018
Inadequate answers provided, got is solved on a different thread.  Posted the link. http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28594076.html#a40553854
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question