Hi Ian,

Ian M wrote:
I have the following formula in Sheet1 of my workbook:

=SUM(IF((Sheet2!$E$1:$E$1000="Mary")*(Sheet2!$G$1: $G$1000="BrownEyes"),1,0))

However, if I delete Column E in Sheet 2, the formula changes to the
following:

=SUM(IF((Sheet2!#REF!="Submitted")*(Sheet2!$F$1:$F $1000="MAS"),1,0))

I thought that by putting dollar signs ($) in front of the cell
locations, this would not happen and that the formula would return
whatever values were in the new column E, the old one having been
deleted.

The reason column E gets deleted, is that I copy a whole dump from a
*.csv file into the Sheet2 of workbook, which then automatically
reformats it, including deleting several unwanted columns.

=SUM(IF((INDIRECT("Sheet2!$E$1:$E$1000")="Mary")*( Sheet2!$G$1:$G$1000="BrownEyes"),1,0))

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)