Refreshing data results in "invalid cell reference"

[Greg Allen]

I imported some data from a CSV into a worksheet. On another worksheet
(named Chapel)
I referenced the data in a formula:

{=SUM(IF(Chapel!$A$1:Chapel!$A$1000=Categories!$A1,
IF(Chapel!$C$1:Chapel!$C$1000=Categories!A$30,1,0),0))}

This formula works fine.

Now, if I go and refresh my data I get invalid references in my formula:

{=SUM(IF(Chapel!$A$1:Chapel!#REF!=Categories!$A1,
IF(Chapel!$E$1:Chapel!$E$1000=Categories!A$30,1,0),0))}

Is there some way to make this work? And why do some references to Chapel
change but others do not?
Notice that the first reference to $A$1000 is now invalid while the
reference to $A$1 stayed the same, and
later references to Chapel changed from "C" to "E". Why is that?

Thanks,

-- Greg Allen

 

[Greg Allen]

Has nobody else seen this? This seems like it would be a common problem.

-- Greg

 

[Dave Peterson]

I've never seen this problem. You may want to explain how the CSV file gets
updated.

And I would think this
{=SUM(IF(Chapel!$A$1:Chapel!$A$1000=Categories!$A1,
IF(Chapel!$C$1:Chapel!$C$1000=Categories!A$30,1,0),0))}

would be more usually seen as:

{=SUM(IF(Chapel!$A$1:$A$1000=Categories!$A1,
IF(Chapel!$C$1:$C$1000=Categories!A$30,1,0),0))}

But I don't have any idea if that will fix your problem.