Aug 01 2020 12:41 PM
Hello all,
I needed some assistance with some formulas. Looking for column B starting @ B5 to auto populate with a yes or no depending on the answers in columns D thru I. If they are all yes, then B would populate yes. If any of D through I is no or empty then B would be no. Also, is there a formula to have a drop down yes or no box in columns D thru I? Thanks in advance for any input and help!
Aug 01 2020 01:21 PM
Aug 01 2020 01:27 PM
Aug 01 2020 01:47 PM
Aug 01 2020 02:08 PM
SolutionIf you find this helpful, please mark it as Like (click thumbs up) and as "Best Answer", it will be beneficial to more Community members reading here.
Nikolino
I know I don't know anything (Socrates)
Aug 01 2020 02:32 PM
Aug 01 2020 02:37 PM
Aug 01 2020 02:55 PM
Aug 01 2020 03:12 PM
I hope it is now your wish. If not please give a short feedback.
Wish you a pleasant day.
Nikolino
I know I don't know anything (Socrates)
Aug 01 2020 03:16 PM
Aug 01 2020 03:21 PM
Aug 01 2020 05:23 PM
@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
Aug 01 2020 05:53 PM - edited Aug 01 2020 05:58 PM
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?
Aug 01 2020 06:59 PM
@bharker , A variation to the if formula. Will work if you needed to insert additional columns in the future.
Aug 01 2020 08:10 PM - edited Aug 01 2020 08:31 PM
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
Aug 01 2020 08:20 PM
Aug 01 2020 08:57 PM
@bharker Glad you liked it!
I've reattached the file. I forget that filling with formats also replicates (and messes up) cell borders.
I corrected the border formats, but you probably already caught my screw up. ~RonR
Aug 01 2020 09:37 PM
Aug 01 2020 02:08 PM
SolutionIf you find this helpful, please mark it as Like (click thumbs up) and as "Best Answer", it will be beneficial to more Community members reading here.
Nikolino
I know I don't know anything (Socrates)