Prev: Combo Box
Next: If Vlookup Question
From: Geoff on 11 May 2010 13:20
I have a spreadsheet in 2007 that I need to save in 2003 format so some
people can use it. The table records are in rows. Here's what one equation
looks like in 2007…

=SUMPRODUCT((Summary[[#This Row],[PartNo]]=PartNo)*(Summary[[#This
Row],[RecDate]]=RecDate))

When I Save As an Excel 2003, the formulas get changed. Here's what it
converts to in Excel 2003…

=SUMPRODUCT((Summary!$A19=PartNo)*(Summary!$B19=RecDate))

All fine and dandy so far. It looks like to me that the column references
are locked, but the row references are "floating". However, all of the
calculations get jumbled when sorting in Excel 2003. For example, after
sorting the above equation (and the entire record) are in row 11. The
equation should become...

=SUMPRODUCT((Summary!$A11=PartNo)*(Summary!$B11=RecDate))

But it doesn't. The row references continue to refer to row 19 which is an
entirely different record.

 | 
Pages: 1
Prev: Combo Box
Next: If Vlookup Question