Conditional Formatting and Adding Text to a Cell based on Multiple Cells

[KCSimmons79]

Hello!

Firstly, thanks for this forum. This is the first time I had to register and post my own question, but I've used your site to figure out a dozen problems I had before.

I'm going to start with the end-state im looking for, but if anyone can provide just one piece to this puzzle it will make my life easier. I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.



This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells. I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree. I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.

I'm a little tired so I may have left out an important part to my question I'm not seeing. Sorry if that's the case. Any help is appreciated. I apologize if I missed a thread that answered this. I spent atleast an hour searching, its a hard question to phrase for a forum or google though. Thanks again for any help guys.

Kyle.

 

[ChrisR]

Hi,

Try this:-

=IF(AND(C6>90,E12>5),"U,T",IF(C6>90,"T",""))

Just change the 5 for whatever your UV index limit is.

Regards,
Chris

 

[KCSimmons79]

Okay!

So I feel like you're definitely on the right track. I wish I was fluent enough with computers to understand the logic that goes into these rules. It's not quite working though. Right now the "T, U" only shows up in the cell when BOTH of those parameters are met. If I for instance reduce the temperature or the uv index to a value below the set number, both letters disappear, not just the one that no longer meets the requirements. Also, what would I do in an instance where I have even more than 2 cells I need to reference? Some of these rows will also need to read data in the cloud cover, wind, visibility, and precipitation cells.

Again thanks for the help. You're giving me hope,
Kyle.

 

[KCSimmons79]

Alright, so I made some progress. I figured out the conditional formatting, the auto-updating powerpoint link, and almost ALL of the formulas. I am however stumped on just this one.

Okay, so I simplified my example, but basically what we have here is one cell that I need to reflect 4 cells of data. In the four cells referenced, if the value is greater than or equal to a specific limit, I need a letter identifier to be placed in the one cell. So if the limit is reached in the first 2 cells, the one cell would say, "T, W". I have it figured out for 3 sources of data, but when I try to run 4 sources I reach my limit on the total number of nester IF's per formula. I'm using 2007. I also don't want to get into the VB scripting side. I want to handle all of my business in that cell. Here is what it looks like.



I've seen other references to bypassing this 7 nested IF function problem, but I haven't been able to understand them and apply it to my specific formula. So please don't just get mad and refer me to one of those threads. I'm learning, slowly. :P

Thanks again for any help.
Kyle.