SOLVED

# Need assistance with formulas

Occasional Contributor

# Need assistance with formulas

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

# RE: Need assistance with formulas

I can not add Maps in the ribbon , Even Ideas not work

# Betreff: Need assistance with formulas

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

# Betreff: Need assistance with formulas

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 (Occasional Contributor)
Solution

# Betreff: 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.

# Betreff: Need assistance with formulas

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.

# Betreff: Need assistance with formulas

Also, nice job on the drop down boxes in column D. Can E,F,G,H, & I also have these same drop downs?

# Betreff: Need assistance with formulas

I just figured this one out. I dragged the cells and the formula populated.

# Betreff: Need assistance with formulas

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)

# Betreff: Need assistance with formulas

sry wrong sheet...here is the right worksheet

Nikolino

# Betreff: Need assistance with formulas

Brilliant! You saved my day!
Du hast meinen Tag gerettet!

# Betreff: Need assistance with formulas

@bharker  Looks like @Nikolino 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

# Betreff: Need assistance with formulas

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?

New sheet

# Betreff: Need assistance with formulas

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

Awesome! Thanks.

# Betreff: Need assistance with formulas

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

# Betreff: Need assistance with formulas

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