Static Count blank

[Luke]

This formula counts blank cell since the last entry in the colmn.
=IF(L3<>"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.99999999999999E+307,$L$2:L2))
:L3),"")

I find myself needing to manually copy it down to get it to accept the
newest entry.

I have put the formula into a new sheet and make reference to the original
sheet
=IF(Sheet1!L3<>"",COUNTBLANK(INDEX(Sheet1!$L$2:L2,MATCH(9.99999999999999E+307,Sheet1!$L$2:L2))
:Sheet1!L3),"")

Is there a way to make this formula say, a full row reference, that will
automatically count blank cells since the last entry so I don't have to
continually update the formula?

Hope this makes since,
Thank you now, and well into the future for your graciousness,
Luke

 

[T. Valko]

It's not clear what you want.

I mean, it's obvious you're counting empty/blank cells but it's not clear
what range you want to use for the count. It seems you want to define an end
of range and count from the last numeric entry to the end of range but
you're formula is using a different end of range each time you copy the
formula down the column.

Need more details.

 

[Luke]

Thanks T. Valko,
Try this:
Sheet1 A:A is automatically updated with numeric information as it occurs
(by a completely different formula).
In sheet2 $A$1 I need a formula that will count blank cells since the last
entry in Sheet1 A:A WITHOUT copying/Filling the formula down in Sheet2 $A$1.

In otherwords, the formula in Sheet2 $A$1 needs to stay static/stay-put
regardless of movement in Sheet1 A:A

I hope this is more clear
Luke

 

[T. Valko]

Maybe this:

=IF(COUNT(Sheet1!A:A),COUNTBLANK(Sheet1!A1:INDEX(Sheet1!A:A,MATCH(1E+100,Sheet1!A:A))),"")

 

[Luke]

T. Valko,
I thought you had it for a minute there but it is returning the MAX value of
the colmn. i.e. the blank cells equal 23 since the last entry and the formula
is returning 8113 which just happens to be the max value in the column
selection.
It took me a bit to catch on what it was returning.

any solutions? I can up load if you need.
Luke

 

[T. Valko]

I can up load if you need.

OK, that'd be a good idea.

This should be relatively easy but I'm just not understanding exactly what
you want. Seeing things will help.

 

[Luke]

T. Valko
Sorry for the slow response.
Here it is: http://www.freefilehosting.net/download/3i5bg

I abreviated this book a lot but the idea is there. Although I'd rather not,
I am wondering now that there might have to be some sort of reference in
sheet1 i.e. date along with the data.

 

[T. Valko]

Ok, I have the file open in front of me.

The formula result is 75. What result do you expect?

Let's see if I might have finally figured this out.

The last numeric entry is in cell A89. The next to the last numeric entry is
in cell A80. You want to count the blank cells between those 2 entries? If
that's the case, will there *always* be at least 2 entries somewhere in the
range?

 

[Luke]

T. Valko,

"You want to count the blank cells between those 2 entries?"

No it is since the last. Hence "> > I am wondering now that there might have
to be some sort of reference in sheet1 i.e. date along with the data."

The numbers in Sheet1 are the rendered from another workbook in which are
the raw data defined with dates. So if there needs to be a reference point
try it with this new upload.
http://www.freefilehosting.net/download/3i5cj
BTW I appreciate what you are doing. I believe you have helped me in the
past and you need to know you are well liked.
Luke

 

[Luke]

opps
You asked: "If

that's the case, will there *always* be at least 2 entries somewhere in the
range?"

Definitely..

Thanks,
Luke

 

[T. Valko]

Ok, try this:

Assuming that the date range is a contiguous block.

=COUNT(Sheet1!A2:A1000)-LOOKUP(1E100,Sheet1!B2:B1000,ROW(A2:A1000))+1

Make sure you set calculation to automatic. Your sample files were set to
manual.

 

[Luke]

T. Valko,
Thanks for hanging in there with me. your formula appears to be working. I
haven't had much time to devote to it today but I'll get after it in the
morning and then post back with the verdict.
Luke

 

[Luke]

T. Valko,
That did it! Thank you very much. It took me a while because Excel 2007 is
slower than a slug with salt on it. I only have one processor and it kept
calculating all the way to a grinding halt, which, is why the manual calc was
in place.

I decided to let it run auto calc and It took 4 hours to finish. go figure.
I would go back to my earlier version but I need the horizontal flow that
2007 provides.

Thanks again for your help

 

[T. Valko]

You're welcome. Thanks for the feedback!

Excel 2007 is slower than a slug with salt on it.

I hear ya!