sumproduct error when adding new sheet

[Gerry]

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)*(ACSALES2008!$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 Deitrick]

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

 

[Joel]

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]

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

 

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

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

Thanks for the help guys.