Formulas returning errors when reference columns are deleted

[Ian M]

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.

I have already laboriously typed in a whole table of formulas like
those above in Sheet1, and want them to be unchanged by the
reformatting.

How can I prevent this happening?

Kind regards

Ian M

 

[Melanie Breden]

Hi Ian,

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)