Static Count blank
 

This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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?

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the
colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the
colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the
colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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

Static Count blank
 

You're welcome. Thanks for the feedback!

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

I hear ya!

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

Maybe this:

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

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke" wrote in message
...
This formula counts blank cell since the last entry in the
colmn.
=IF(L3<"",COUNTBLANK(INDEX($L$2:L2,MATCH(9.999999 99999999E+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