[Solved] Formulas: Aaaaaargh !!!!

  • I have a spreadsheet that has a name/staus/month column. I would like the status column (cell) to turn red if the person has not paid his dues that month. For example, if Mr. Excel has not paid his $40 for the month of Aug, the staus cell turns red with a "overdue" text or something like that. I cant get the "IF" formula to work with multiple columns. Help !!!!!!:(

  • I have 12 columns for the 12 months in the year. How cabn I get "IF" to look at the entire row of months for each persons name?:o

  • See if the attached helps. There are formulas in the empty cells as well as the ones in the month columns that diplay text. I used $50 as the expected payment and flagged payments that were lower.

    All of the color coding is done with conditional formatting.

  • P.S. I forgot: WELCOME TO THE FORUM.

    Also, as you enter payments, the payment value replaces the formula in that cell.

    And one more thing. When you start a thread here, please add new posts on that topic into the same thread. It helps folks keep track of what relates to what. (If there a Moderator around, can you combine these two threads?)

  • Here is a slight improvement onwhat I posted earlier. If someone's total payments are up to date without any LATE entries, it places ?? in the status column, bit does not turn it yellow.

    Note that if someone pays early (i.e., a double payment in July), then you have to enter a 0.00 in August to keep the falg from going "OVERDUE" and Red. It still goes "??" white because of the non-standard payments.

    ALso, my formulas assumed that payments were due on the 1st of each month.

    Hope this all helps.

  • Background colours can be changed with conditional formatting (click on Format-Conditional Formatting).
    You mention an IF covering multiple columns (sounds like a case for =SumProduct) but you have not given enough info about which columns or how the data is organized for us to give a specific solution.

    PS: Bad. Bad Mr Excel. I always suspected.... :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!