Return YES NO LATE from multiple tick or cross!

[MarkTheAccountant]

Hi All, Great site you got here....

I need a little help from you 'Guru's' out there....please


I want a cell to return a text of YES NO or LATE from a range of cells that we have selected with
a tick, a cross, or a question mark (a,r,s in Marlett font) and also to calculate the amount outstanding from the cell next to it.

I have taken extracts from my Rental Manager spreadsheet that I have created and put it into a Sample file

I have used 2010 for the sample but the working spreadsheet will run on 2003, 2007 & 2010


Many Many Thanks in Advance and I do hope it is possible!


Mark

[JBeaucaire]

For 2007+

K16: ==LOOKUP(COUNTIFS($B$7:$B$19, "<=" & TODAY(), $E$7:$E$19, "r"), {0,1,2}, {"NO","LATE","YES"})
L16: =SUMIFS($D$7:$D$19, $B$7:$B$19, "<=" & TODAY(), $E$7:$E$19, "r")


You should retire the Excel 2003. For 2003:

K16: =LOOKUP(SUMPRODUCT(--($B$7:$B$19 <=TODAY()), --($E$7:$E$19="r")), {0,1,2}, {"NO","LATE","YES"})
L16: =SUMPRODUCT(--($B$7:$B$19<=TODAY()), --($E$7:$E$19="r"), $D$7:$D$19)

[MarkTheAccountant]

Hi JBeaucaire,

Thanks for the speeedy reply, however I can only get it to return NO and not YES or LATE however I alter E7 to E19

Any Ideas

Ps we are phasing 2003 out but still have a few stations to upgrade.

Cheers
Mark

[JBeaucaire]

Your workbook with those formulas:

[MarkTheAccountant]

Hi JBeaucaire, Cheers Thank You...

I have transferred the code into my worksheet and I'm having the same problem...
it has automatically put the code like this '1'!$K$29:$K$41... etc. etc.

I have narrowed it down to the apostrophes probably causing the problem

the pages in the workbook are Overview, 1, 2, 3, 4, 5, etc. etc. up to 50

Any advice would be great

Thanks Mark

[MarkTheAccountant]

Just to add when I used your Sample2 xls file and split it over 2 sheets and altered the code it works fine

attached is your altered file!

I can email over my original if it helps

I don't want to upload but I can if really necessary

Cheers
Mark

[JBeaucaire]

yes, your uploaded file seems to work fine. So....?

[MarkTheAccountant]

yes, your uploaded file seems to work fine. So....?

I originally created this in 2003 so I wouldn't have any trouble going back from 2007 or 2010
would that cause the problem?

attached in the whole file if you wouldn't mind having a look for me!

the password is "please"

cheers

Mark

[JBeaucaire]

The formula doesn't work because the "date strings" in your column K aren't real dates, they are text. The drop downs are fine, but not all that necessary.

1) Remove the data validation from those cells
2) in K29: 9/1/2011 (formatted to display as Month Year, if you wish)
3) in K30: =EOMONTH(K29,0)+1
4) copy K30 down

Now you have a "self-incrementing" list of real dates, so the formula on Overview sheet now shows this customer is in ARREARS.

[MarkTheAccountant]

Thanks that brilliant.... You are a true Guru!!

Is there a way to have the day populated in the same manner, because I know there going to ask!

Is there any other advise you could give to hopefully make the site more 'unbreakable'

I will do all the protection when its nearly ready (cells sheets etc.)

Cheers

Mark

PS Just found your link to your site...I'll make a donation when I'm back at work to say thanks...

[JBeaucaire]

Is there a way to have the day populated in the same manner, because I know there going to ask!

Please elaborate.

[MarkTheAccountant]

I've done the alterations as suggested and it now works fine.

but if somebody enters say 14th April 2012 the following month shows as 1st May 2012 etc. etc

Thanks
Mark

[JBeaucaire]

Ongoing rents are due on the first of the month. Your original sheet proved that since you didn't even want to include the actual dates. Why would you introduce further complexity?

So you mentioned "Since they'll ask", to which I would say the answer is "Put in the 1st of the first month to be recorded on this sheet."

As noted earlier, FORMAT those cells to be "MMMM YYYY" and it won't matter, they won't see the dates. But they should still put in the 1st for the first cell. You want the formulas on the other sheet to see the dates correctly.