ExcelBanter - Date with links

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Date with links (https://www.excelbanter.com/excel-worksheet-functions/8467-date-links.html)

My ss contains links to another spreadsheet. In col A are dates. I need to
add only those pieces of information that are valid dates and not include
the links, but I'm having a bit of difficulty. Here is the formula I used,
but I get #value error.

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004),--ISNUMBER(--(MONTH(Burlington!$A$2:$A$201))))

Maybe is not possible?

Thanks!

Sounds like you have text as opposed to numeric dates, also you can shorten
that to

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$201)=2004))

Regards,

Peo Sjoblom

"Annette" wrote:

My ss contains links to another spreadsheet. In col A are dates. I need to
add only those pieces of information that are valid dates and not include
the links, but I'm having a bit of difficulty. Here is the formula I used,
but I get #value error.

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004),--ISNUMBER(--(MONTH(Burlington!$A$2:$A$201))))

Maybe is not possible?

Thanks!

It still results in #value ... in col A, there are links to another
spreadsheet which update when the file is opened. So the links look
something like this in each cell

=IF('\\Blacr2s3\ECSU\Medical Referrals From NPA Conversion\[Burlington
P.xls]Sheet1'!A32="","",'\\Blacr2s3\ECSU\Medical Referrals From NPA
Conversion\[Burlington P.xls]Sheet1'!A32)

When the update is made to the column, it appears as a date and in the ss I
have the format set to date. I only want to add by month those cells that
have dates only. (Hope this makes more sense and hopefully you can see my
problem easily)

Thanks Peo

"Peo Sjoblom" wrote in message
...
Sounds like you have text as opposed to numeric dates, also you can
shorten
that to

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004))

Regards,

Peo Sjoblom

"Annette" wrote:

My ss contains links to another spreadsheet. In col A are dates. I
need to
add only those pieces of information that are valid dates and not
include
the links, but I'm having a bit of difficulty. Here is the formula I
used,
but I get #value error.

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004),--ISNUMBER(--(MONTH(Burlington!$A$2:$A$201))))

Maybe is not possible?

Thanks!

Got it! The reason is that you use the date function MONTH and YEAR and when
you have the linked cell is blank you want the date cell blank as well, the
date functions yare, month and day only work on numeric values and will
return #VALUE when you have a text string which "" is

There are some ways to solve this, you can change the link to

IF('\\Blacr2s3\ECSU\Medical Referrals From NPA Conversion\[Burlington
P.xls]Sheet1'!A32="",0,'\\Blacr2s3\ECSU\Medical Referrals From NPA
Conversion\[Burlington P.xls]Sheet1'!A32)

drawback is that you will get a date in Burlington sheet looking like

01/00/00

you can hide that with conditional formatting using a white font and cell
value equal to 0 (assuming the background is white) That way you can use your
formula as is (although the isnumber part is still not necessary) or you can
change the formula to

=SUMPRODUCT(--(Burlington!$A$2:$A$20=--"10/01/04"),--(Burlington!$A$2:$A$20<=--"10/31/04"))

where you would use the first and the last dates of the month you want to
check
(in this case October 2004)

better would be

=SUMPRODUCT(--(Burlington!$A$2:$A$20=F1),--(Burlington!$A$2:$A$20<=G1)

where you can type in the lower date in F1 and the upper date in G1

Regards,

Peo Sjoblom

"Annette" wrote:

It still results in #value ... in col A, there are links to another
spreadsheet which update when the file is opened. So the links look
something like this in each cell

=IF('\\Blacr2s3\ECSU\Medical Referrals From NPA Conversion\[Burlington
P.xls]Sheet1'!A32="","",'\\Blacr2s3\ECSU\Medical Referrals From NPA
Conversion\[Burlington P.xls]Sheet1'!A32)

When the update is made to the column, it appears as a date and in the ss I
have the format set to date. I only want to add by month those cells that
have dates only. (Hope this makes more sense and hopefully you can see my
problem easily)

Thanks Peo

"Peo Sjoblom" wrote in message
...
Sounds like you have text as opposed to numeric dates, also you can
shorten
that to

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004))

Regards,

Peo Sjoblom

"Annette" wrote:

My ss contains links to another spreadsheet. In col A are dates. I
need to
add only those pieces of information that are valid dates and not
include
the links, but I'm having a bit of difficulty. Here is the formula I
used,
but I get #value error.

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004),--ISNUMBER(--(MONTH(Burlington!$A$2:$A$201))))

Maybe is not possible?

Thanks!

AHHHHHHHHHHh ... newt you would get it after seeing more of the example ...
this works just as predicted now. Thanks!

"Peo Sjoblom" wrote in message
...
Got it! The reason is that you use the date function MONTH and YEAR and
when
you have the linked cell is blank you want the date cell blank as well,
the
date functions yare, month and day only work on numeric values and will
return #VALUE when you have a text string which "" is

There are some ways to solve this, you can change the link to

IF('\\Blacr2s3\ECSU\Medical Referrals From NPA Conversion\[Burlington
P.xls]Sheet1'!A32="",0,'\\Blacr2s3\ECSU\Medical Referrals From NPA
Conversion\[Burlington P.xls]Sheet1'!A32)

drawback is that you will get a date in Burlington sheet looking like

01/00/00

you can hide that with conditional formatting using a white font and cell
value equal to 0 (assuming the background is white) That way you can use
your
formula as is (although the isnumber part is still not necessary) or you
can
change the formula to

=SUMPRODUCT(--(Burlington!$A$2:$A$20=--"10/01/04"),--(Burlington!$A$2:$A$20
<=--"10/31/04"))

where you would use the first and the last dates of the month you want to
check
(in this case October 2004)

better would be

=SUMPRODUCT(--(Burlington!$A$2:$A$20=F1),--(Burlington!$A$2:$A$20<=G1)

where you can type in the lower date in F1 and the upper date in G1

Regards,

Peo Sjoblom

"Annette" wrote:

It still results in #value ... in col A, there are links to another
spreadsheet which update when the file is opened. So the links look
something like this in each cell

=IF('\\Blacr2s3\ECSU\Medical Referrals From NPA Conversion\[Burlington
P.xls]Sheet1'!A32="","",'\\Blacr2s3\ECSU\Medical Referrals From NPA
Conversion\[Burlington P.xls]Sheet1'!A32)

When the update is made to the column, it appears as a date and in the
ss I
have the format set to date. I only want to add by month those cells
that
have dates only. (Hope this makes more sense and hopefully you can see
my
problem easily)

Thanks Peo

"Peo Sjoblom" wrote in message
...
Sounds like you have text as opposed to numeric dates, also you can
shorten
that to

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004))

Regards,

Peo Sjoblom

"Annette" wrote:

My ss contains links to another spreadsheet. In col A are dates. I
need to
add only those pieces of information that are valid dates and not
include
the links, but I'm having a bit of difficulty. Here is the formula
I
used,
but I get #value error.

=SUMPRODUCT(--(MONTH(Burlington!$A$2:$A$201)=10),--(YEAR(Burlington!$A$2:$A$
201)=2004),--ISNUMBER(--(MONTH(Burlington!$A$2:$A$201))))

Maybe is not possible?

Thanks!