Need to use the reference in a defined name in adifference colum

[HammerJoe]

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?

 

[Conan Kelly]

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

 

[HammerJoe]

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











- 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 D1500 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.

 

[Conan Kelly]

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

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











- 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 D1500 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]

Hi,

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

 

[Conan Kelly]

Glad to help.