Counting Blank Cells

[Quicksnot]

hi again guys, thanks for all you previous help ,

I have another problem yet to solve, I have a sample book attached of what I'm trying to accomplish.
john has a bunch of places to collect money at from a video game I put in a location, but he has
not collected 1 of them the last 2 days, on the sheet1 its C500, on sheet2 is the date, and his name on top,
below is the code of the place and the name of the location, I'm using the word "Place" as the location name
for now, if you look at sheet2, you will see a number in red 3 after the location, which means it will be 3 weeks
since that location has been collected. is there a way that when a place hasn't been collected in 2 or more weeks
that a red number will be put in the same cell as the place as shown on sheet 2?

Thanks
Keith

[Richard Buttrey]

Was this workbook created with Excel for Mac.

I ask since the dates are coming through as 2021 rather than as I suspect they should be 2017?

[Richard Buttrey]

This formula will return the Location and the count of blank cells.
To colour the number different to the rest of the text would need a macro.

B2 copied down

Please Login or Register  to view this content.

[Quicksnot]

Just used a future date, it really doesn't mean anything right now

Thanks
Keith

[Quicksnot]

Hi Richard

where would I paste that formula? sorry I'm still new to excel

Thanks
Keith

[Quicksnot]

I paste the formula in b2 on sheet1, no luck, same with pasting it in b2 on sheet2 could you try doing it and sending it back
with the formula pasted in the right spot, I must be doing something wrong.

Thanks
Keith

[JeteMc]

On Jan. 22, the next listed collection date, it will have been three weeks since he collected. I modified Richard's formula to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Quicksnot]

thanks JeteMc but I'm having some weird issues trying to download the excel file, it will only save as a php,
and I try copying the formula to the original excel book and I get #REF trying to paste it to sheet2 cell B2.

please help
Thanks
Keith

[JeteMc]

I don't know why the .xlsx file would not download but I'm reattaching. I have noticed that sometimes if a long formula doesn't paste correctly into a cell, I have to paste it into the formula bar while the cell is selected.
Let us know if you have any questions.

[Tony Valko]

thanks JeteMc but I'm having some weird issues trying to download the excel file, it will only save as a php

That's a recent bug in the forum software.

http://www.excelforum.com/showthread.php?t=1170626

If all else fails just rename the file and give it an Excel file extension.

[Quicksnot]

that worked perfect thank you very much

thanks
keith

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[Quicksnot]

I was wondering if you felt like helping a bit more, I have tried to put that formula into that actual workbook I needed that in, but not much luck .

I attached the real work book, but let me explain a little bit of the problem, the boss asked me if I could sort the guys places they stop at, and show a number
after there name in red, when they are 3 or more weeks behind collecting the place, there's no personal information on the sheet. the $188.00 money value is
a made up number just for testing.

sheet1 = the bosses copy which he doesn't really want it changed as far as appearance
sheet2 = the guys places they got to separated by code and location for each guy
sheet3 = the bosses sheet without the colors and extra stuff he put on it
sheet4 = the sheet that has a few macros on it that I made to separate their city route and their county routes

the formula that I tried to put in that you sent me , almost works right, don't know what maybe I altered wrong
but on sheet3 the numbers are 1 off, says 3 blanks under the date picked, but should be 4 including the Colum of the date.
and there will be blank cells in the rows like sheet 1 shows and it counts them too, 188,blank,188,blank,blank,blank it shows 4, should only show 3,
I hope I'm explaining this alright.

Thanks
Keith

[JeteMc]

Attempting to accomplish the goals of counting only dates between the last collection to the date displayed in Sheet3!A1 also to apply red font to the cell if the last collection was three or more weeks ago (although as Richard said "To colour the number different to the rest of the text would need a macro").
This proposed solution uses two helper columns on sheet 2 columns XFC3:XFD3 and down which are populated by the formulas (respectively):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

There is also a formula in Sheet2!XFC2 as follows:

Please Login or Register  to view this content.

The formula that populates sheet3 column B, except for where a name is already entered is:

Please Login or Register  to view this content.

The formula that serves a conditional formatting rule for those cells is:

Please Login or Register  to view this content.

In sheet 2 rows 3:19 I put in all of the collection possibilities and the results seem to work, since you said to display numeric values of three or greater only.
Let us know if you have any questions.

[Quicksnot]

works awesome thank you

Keith

[JeteMc]

You're Welcome. Thank you for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[Quicksnot]

Hi again JeteMc

to change it from 3 or more, to 2 or more is this the formula that changes that?

Please Login or Register  to view this content.

and where is the code that makes the text red?
sorry, but I'm new to this stuff, and don't play with it often enough to remember what I do learn , thank god for people in this forum

thanks again
Keith

[JeteMc]

Yes to change to 2 or more change the formula in Sheet2!XFD3 and down so that it reads:

Please Login or Register  to view this content.

The code that marks the text red is in conditional formatting for Sheet3. If you select Sheet3 cell B2, then select Conditional Formatting, then Manage Rules, you will see the formula.
Let us know if you have any questions.

[Quicksnot]

Thanks JeteMc

works fine

Keith

[JeteMc]

You're Welcome and Thank You for the feedback. I hope that you have a blessed day.