Hi,

I have two columns with dates on it.
Column C is used with effective dates (Effective Dates) and column D
is for Creation Dates.

Coulmn C cells can be empty (not created) while column D will always
contain a date.
Also the Effective date and the creation date can be different on the
same row.

I have defined a CreatedDate name for column D using Offset to
dynamically expand the range.
I need to do the same for column C, problem is that there are blanks
in it so the Offset (counta) formula does not seem to work.

Heres what I need.
I want to be able to count (using sumproduct I guess) the number of
created dates on column C that match the date range that Is selected.,
ie from 2008/01/01 to 2008/01/10 entered in a a cell somewhere on the
spreadsheet but the formula has to be dynamic, it needs to check all
the rows on coulumn C.
Needs to check the same number of rows as Column D count has.


Please how can this be done?

HammerJoe,

How about counting the dates in column D for your offset in Column C. That
would create a dynamic named range for column C that will always
expand/contract with the dynamic named range for column D (they will always
be the same size).

If I've misunderstood, please write back,

HTH,

Conan




wrote in message
...
Hi,

I have two columns with dates on it.
Column C is used with effective dates (Effective Dates) and column D
is for Creation Dates.

Coulmn C cells can be empty (not created) while column D will always
contain a date.
Also the Effective date and the creation date can be different on the
same row.

I have defined a CreatedDate name for column D using Offset to
dynamically expand the range.
I need to do the same for column C, problem is that there are blanks
in it so the Offset (counta) formula does not seem to work.

Heres what I need.
I want to be able to count (using sumproduct I guess) the number of
created dates on column C that match the date range that Is selected.,
ie from 2008/01/01 to 2008/01/10 entered in a a cell somewhere on the
spreadsheet but the formula has to be dynamic, it needs to check all
the rows on coulumn C.
Needs to check the same number of rows as Column D count has.


Please how can this be done?

On Jan 28, 6:37*pm, "Conan Kelly"
wrote:
HammerJoe,

How about counting the dates in column D for your offset in Column C. *That
would create a dynamic named range for column C that will always
expand/contract with the dynamic named range for column D (they will always
be the same size).

If I've misunderstood, please write back,

HTH,

Conan

wrote in message

...



Hi,

I have two columns with dates on it.
Column C is used with effective dates (Effective Dates) and column D
is for Creation Dates.

Coulmn C cells can be empty (not created) while column D will always
contain a date.
Also the Effective date and the creation date can be different on the
same row.

I have defined a CreatedDate name for column D using Offset to
dynamically expand the range.
I need to do the same for column C, problem is that there are blanks
in it so the Offset (counta) formula does not seem to work.

Heres what I need.
I want to be able to count (using sumproduct I guess) the number of
created dates on column C that match the date range that Is selected.,
ie from 2008/01/01 to 2008/01/10 entered in a a cell somewhere on the
spreadsheet but the formula has to be dynamic, it needs to check all
the rows on coulumn C.
Needs to check the same number of rows as Column D count has.

Please how can this be done?- Hide quoted text -

- Show quoted text -

Hi,
I thought of that but how do you use it on column C?
Lets say I have 500 rows.
The dynamic name range will give D1:D500 as it should.
How do I use it as C1:C500?

Ohhh, you mean to use offset?
If I understand Offset allows to look at one column using another as
range correct?
I will see what I can come up with.

HammerJoe,

I'm assuming your OFFSET function for "CreatedDate" is something like this:

=OFFSET($D$2,0,0,COUNT($D$2:$D$65536),1)

Just make your OFFSET function for "EffectiveDate" like this:

=OFFSET($C$2,0,0,COUNT($D$2:$D$65536),1)

You are counting column D to dynamically change the height of "CreatedDate".
Just count column D again to dynamically change the height of
"EffectiveDate"

HTH,

Conan




wrote in message
...
On Jan 28, 6:37 pm, "Conan Kelly"
wrote:
HammerJoe,

How about counting the dates in column D for your offset in Column C. That
would create a dynamic named range for column C that will always
expand/contract with the dynamic named range for column D (they will
always
be the same size).

If I've misunderstood, please write back,

HTH,

Conan

wrote in message

...



Hi,

I have two columns with dates on it.
Column C is used with effective dates (Effective Dates) and column D
is for Creation Dates.

Coulmn C cells can be empty (not created) while column D will always
contain a date.
Also the Effective date and the creation date can be different on the
same row.

I have defined a CreatedDate name for column D using Offset to
dynamically expand the range.
I need to do the same for column C, problem is that there are blanks
in it so the Offset (counta) formula does not seem to work.

Heres what I need.
I want to be able to count (using sumproduct I guess) the number of
created dates on column C that match the date range that Is selected.,
ie from 2008/01/01 to 2008/01/10 entered in a a cell somewhere on the
spreadsheet but the formula has to be dynamic, it needs to check all
the rows on coulumn C.
Needs to check the same number of rows as Column D count has.

Please how can this be done?- Hide quoted text -

- Show quoted text -

Hi,
I thought of that but how do you use it on column C?
Lets say I have 500 rows.
The dynamic name range will give D1:D500 as it should.
How do I use it as C1:C500?

Ohhh, you mean to use offset?
If I understand Offset allows to look at one column using another as
range correct?
I will see what I can come up with.

Hi,

I didnt think of that, it is even better, it makes the formular
smaller and easier to read.
Thanks for the help.

Glad to help.


wrote in message
...
Hi,

I didnt think of that, it is even better, it makes the formular
smaller and easier to read.
Thanks for the help.