Forum Discussion
Need assistance with formulas
- I'm not sure because the translation is not the best ... but here is an example.
Have a dro down menu built in D, at the same time the formula is built in B.
Du hast meinen Tag gerettet!
bharker Looks like NikolinoDE provided the exact solution you wanted.
However, being a past property manager, myself, I couldn't help but notice a minor issue. Your column G is labeled "Equipment/Repairs Needed?" Putting a "Yes" in this column would seem contrary to having a unit "Rent Ready."
My first thought was to change the column heading to "Equipment/Repairs Completed" so as to be consistent with other columns and require a "Yes" to get a "Yes" in column B. However, entries in G like "woodwork" or "A/C broken" might then be misconstrued as repairs completed - rather than as repairs required.
Alternatively, you could change the formula in B to require a "No" in G and a "Yes" in the others. Here is the modified formula:
=IF(AND(D5="Yes",E5="Yes",F5="Yes",G5="No",H5="Yes",I5="Yes"),"Yes", "No")
Whichever way is less confusing is up to you, of course. Personally, I'd think requiring "Yes" in all columns makes more sense. While not a direct reply to your question, I hope you find my comments helpful. ~RonR
- I didn't catch that at all. Thanks for updating. Much appreciated!
- Wholly cow! Exactly what I was looking for. I really like the 100+ days vacant highlight.
Here is your file modified to get Red/Green conditional formatting you wanted. Excel has many sample formats available, I just picked a couple. Here's what I did:
Select the cell B5. Go to Home > Conditional Formatting on the ribbon. Select Highlight Cells Rules > Equal to ... and enter "No" in the blank. Select the "Red Text" format from the drop down and OK. Repeat the steps except fill in "Yes" this time. The drop down for the format offers Dark Green text on Green fill. To get just Green text, you'd have to select "custom" (more about that below).
Once you have formats set for B5, use the fill handle (+) to replicate down the column -- just be sure to select "Fill Formatting Only" in the pop-up menu so you don't copy over your values. You can also use COPY - Paste SPECIAL to copy just the formatting, not values and formulas.
In cells D5:I5, I set the "No" format as before. To get Green Text (without fill), you need to select "custom" format. There I selected Green for color, but also selected Bold text to make it stand out more. As before, use the fill handle to fill formatting only down the rest of the table.
Just for fun, I added one more set of rules. In Column C, I added the rule to format "Greater Than 100" in Red text on Red fill. This raises the "red flag" on units that have been vacant for more than 100 days. Just an example of the many options available.
You can change any of these formats by selecting the cells you want to alter and going to Conditional Formatting > Manage Rules ... and Add/Delete or Edit as you wish. There's a bit of a learning curve, but the results can be very gratifying. If things get balled up with too many rules (I do it all the time), just select Clear Rules, from cells or the entire sheet, and start over. Happy formatting! ~RonR
- Awesome! Thanks.
Yes, I caught that as well once I started filling it out. Is there a way if B is yes to have the cell highlighted green, and if no highlighted in red?