Excel 2007 : Formulas lose cell references

I don't believe it's the paste function that you are having a problem with. I believe you are probably DELETING the previous information from the FRIDAY DATA spreadsheet instead of CLEAR CONTENTS each time you want to paste new information into the spreadsheet. DELETING will actually remove the cells you referenced and change the cell reference to #REF!. Try highlighting and CLEAR CONTENTS instead - this will keep the cells reference in the formulas.

Example:

=A1

If you DELETE A1, it no longer exists and is actually replaced...so the formula will change to =#REF!
If you CLEAR CONTENTS, A1 still exists...just no longer has anything in it...so the formula will stay =A1

Hope that helps

GWB,

As GuruWannaB states the #REF error normally emanates from source being deleted - either sheet being deleted or cells within a sheet that are themselves precedents of the formula now generating the #REF! error... normally this is done manually but it can also result from refreshing data via external queries as these tend to completely delete the old data & subsequently replace it with new (thereby generating #REF errors on dependent cells) - you may not "see" this happening and may presume the cell values are simply being refreshed but this is often not be the case (cells are first deleted then re-inserted as new).

A possible non-volatile workaround (ie alternative to INDIRECT) can be to use INDEX, ie:

instead of:

=SUMPRODUCT(--('friday data'!$P$2:'friday data'!$P$65000))

you could use:

=SUMPRODUCT(--(INDEX('friday data'!$1:$65000,0,16))

as long as the sheet itself is not deleted the above should hold it's references even if the column of interest is deleted & replaced.

Though let me also state that using such large ranges in what is in essence an Array formula is bad news performance wise...

When you say "that" do you mean using Clear rather than Delete or using INDEX ?

The reason I ask is to do with your other question re: using ranges... the best approach would be to use a Dynamic Named Range but the formula for which will depend on which approach worked for you... ie if you're no longer deleting data you can used fixed references in the DNR formulae else we'd have to use a different INDEX approach... also I'm assuming given the use of SUMPRODUCT to coerce P to number that the values in P are indeed numbers stored as text ?

cnc,

Please take a few minutes to read the forum rules, and then start your own thread.