I have a workbook with many years info in it.

I have copied a current year sheet ACSALES2008 and created
ACSALES2009. When I change the formulas to 2008 from 2007 on
ACSALES2009 I receive a #VALUE! error in each cell.

=SUMPRODUCT((ACSALES2008!$E$5:$E$380=$E7)*(ACSALES 2008!$F$5:$Q$380)*
(COLUMN($F$5:$Q$380)<=5+$S$2))

There seems to be something wrong with the second part of the formula
(ACSALES2008!$F$5:$Q$380). If I change this to 2007 the formula
produces figures. I have tried pasting only the formatting, but that
doesn't work. I copied the entire data in that range from 2007 to
2008 worksheet and it produced figures. I can't see anything
different.

Any suggestions, any help would be greatly appreciated.

TIA
Gerry

I copied the entire data in that range from 2007 to
2008 worksheet and it produced figures. I can't see anything
different.

Perhaps you have some numbers that are actually text. Format F:Q as number, then put the number 1
into a blank cell pre-formatted as a number. Copy that cell, and then select all the cells in F to
Q, right click, choose Paste Special, and then multiply.

HTH,
Bernie
MS Excel MVP


"Gerry" wrote in message
...
I have a workbook with many years info in it.

I have copied a current year sheet ACSALES2008 and created
ACSALES2009. When I change the formulas to 2008 from 2007 on
ACSALES2009 I receive a #VALUE! error in each cell.

=SUMPRODUCT((ACSALES2008!$E$5:$E$380=$E7)*(ACSALES 2008!$F$5:$Q$380)*
(COLUMN($F$5:$Q$380)<=5+$S$2))

There seems to be something wrong with the second part of the formula
(ACSALES2008!$F$5:$Q$380). If I change this to 2007 the formula
produces figures. I have tried pasting only the formatting, but that
doesn't work. I copied the entire data in that range from 2007 to
2008 worksheet and it produced figures. I can't see anything
different.

Any suggestions, any help would be greatly appreciated.

TIA
Gerry

In worksheet help menu enter the following

Correct a #VALUE! error


Try the suggestion from the help page and "Trace Error". I think you have
some bad data in one of the cells.

"Gerry" wrote:

I have a workbook with many years info in it.

I have copied a current year sheet ACSALES2008 and created
ACSALES2009. When I change the formulas to 2008 from 2007 on
ACSALES2009 I receive a #VALUE! error in each cell.

=SUMPRODUCT((ACSALES2008!$E$5:$E$380=$E7)*(ACSALES 2008!$F$5:$Q$380)*
(COLUMN($F$5:$Q$380)<=5+$S$2))

There seems to be something wrong with the second part of the formula
(ACSALES2008!$F$5:$Q$380). If I change this to 2007 the formula
produces figures. I have tried pasting only the formatting, but that
doesn't work. I copied the entire data in that range from 2007 to
2008 worksheet and it produced figures. I can't see anything
different.

Any suggestions, any help would be greatly appreciated.

TIA
Gerry

Bernie I gave that a shot and everything looks good. All blanks
filled in with zeroes as well. Still the same error.

On Jan 20, 1:07*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
*I copied the entire data in that range from 2007 to
2008 worksheet and it produced figures. *I can't see anything
different.

Perhaps you have some numbers that are actually text. *Format F:Q as number, then put the number 1
into a blank cell pre-formatted as a number. *Copy that cell, and then select all the cells in F to
Q, right click, choose Paste Special, and then multiply.

HTH,
Bernie
MS Excel MVP

"Gerry" wrote in message

...

I have a workbook with many years info in it.

I have copied a current year sheet ACSALES2008 and created
ACSALES2009. *When I change the formulas to 2008 from 2007 on
ACSALES2009 I receive a #VALUE! error in each cell.

=SUMPRODUCT((ACSALES2008!$E$5:$E$380=$E7)*(ACSALES 2008!$F$5:$Q$380)*
(COLUMN($F$5:$Q$380)<=5+$S$2))

There seems to be something wrong with the second part of the formula
(ACSALES2008!$F$5:$Q$380). *If I change this to 2007 the formula
produces figures. *I have tried pasting only the formatting, but that
doesn't work. *I copied the entire data in that range from 2007 to
2008 worksheet and it produced figures. *I can't see anything
different.

Any suggestions, any help would be greatly appreciated.

TIA
Gerry

On Jan 20, 1:20*pm, Joel wrote:
In worksheet help menu enter the following

Correct a #VALUE! error

Try the suggestion from the help page and "Trace Error". * I think you have
some bad data in one of the cells.

"Gerry" wrote:
I have a workbook with many years info in it.

I have copied a current year sheet ACSALES2008 and created
ACSALES2009. *When I change the formulas to 2008 from 2007 on
ACSALES2009 I receive a #VALUE! error in each cell.

=SUMPRODUCT((ACSALES2008!$E$5:$E$380=$E7)*(ACSALES 2008!$F$5:$Q$380)*
(COLUMN($F$5:$Q$380)<=5+$S$2))

There seems to be something wrong with the second part of the formula
(ACSALES2008!$F$5:$Q$380). *If I change this to 2007 the formula
produces figures. *I have tried pasting only the formatting, but that
doesn't work. *I copied the entire data in that range from 2007 to
2008 worksheet and it produced figures. *I can't see anything
different.

Any suggestions, any help would be greatly appreciated.

TIA
Gerry

Ok, I did find it, there was a cell with a decimal and no number

Thanks for the help guys.