SOLVED

Need assistance with formulas

Copper Contributor

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!

19 Replies
I can not add Maps in the ribbon , Even Ideas not work
sry but i didn't understand the task. If D is yes or no, you want it to appear in B. If I is no and D yes, then what in B? ... Please repeat the task, as simple as possible, so even beginners as I can understand it :)
Thanks for the reply. If anything in D thru I is not a "yes", then B will be a "no" . If D thru I are all yes, then B is yes.
best response confirmed by bharker (Copper Contributor)
Solution

@bharker 

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.
 
This is nice! So right now if D5 is yes then B5 is yes, but I need D5, E5, F5, G5, H5, I5 to also all be "yes" for B5 to be yes. If any of D5, E5, F5,H5, or I5 = no, then B5 = no.
Also, nice job on the drop down boxes in column D. Can E,F,G,H, & I also have these same drop downs?
I just figured this one out. I dragged the cells and the formula populated.

@bharker 

 

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)

@bharker 

 

sry wrong sheet...here is the right worksheet :)

 

 

Nikolino

Brilliant! You saved my day!
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

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?

@bharker 

New sheet

@bharker , A variation to the if formula. Will work if you needed to insert additional columns in the future.

Awesome! Thanks.

@bharker  

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  

Wholly cow! Exactly what I was looking for. I really like the 100+ days vacant highlight.

@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

I didn't catch that at all. Thanks for updating. Much appreciated!
1 best response

Accepted Solutions
best response confirmed by bharker (Copper Contributor)
Solution

@bharker 

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.
 

View solution in original post