If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.
Hi, I tried to use the search fucntion but it kept giving me memory errors... So I oppologise if this has been answered..
I have a spreedsheet with neumorous formuli over 4 sheets, all of which use cell ranges off each other and a data sheet.
A large amount of data is pasted into the data sheet and most formuli get there cell ranges from here.
The problem Im having is that some times the formuli loose their cell references and I have to manually recreate the formula's.
EG: the formula should be SUMPRODUCT(--('friday data'!$P$2:'friday data'!$P$65000))
However it changes to SUMPRODUCT(--('friday data'!#REF!:'friday data'!#REF!))
I have tried locking the cells with the formuli and protecting the worksheet, however it stills happens. another note would be to add that this does not happen all the time...
could it be something to do with the data being pasted into the 'friday data' sheet?
Location: 55 miles S.E. of the Buckeye Capital!! Go BUCKS!!
MS Office Version:2003
Posts: 376
re: 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.
Quote:
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,
__________________
I help because of the Pavlovian dog that resides in the inner me...so if you are happy with the results, please add to my reputation by clicking the blue scales icon in the upper right portion of the blue bar of the post. It helps keep me motivated!
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Last edited by GuruWannaB; 06-16-2009 at 09:58 AM.
We have experienced this problem too. Currently we have updated to the most current service pack and we rebuilt the file in 2007. It was a very old file, probably originally created in Excel 97. So far the range names have been good.
I too have been having the same problem. I have graded to 2007 and using the same spreadsheet about every 2nd or 3rd day the reference drops off. I still do the same things to the sheets but am getting annoyed having to re-reference cells. I have 2 sheets that look at a 3rd. One has the problems the other has never had this problem-touch wood.
please help
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:
thanks for the replies, been busy as of late so not had chance to check the post.
That seems to have sorted it for now, the reason for the large refs is due to the amount of data.. it can range from anywhere between 1000 rows to 10,000 rows.. so i decided to just set the formula to look at the max rows that excel can handle. is there a better way?
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 ?
My problem is slightly different. The sheet works fine until end of the day, it is when I open it again the next day that the references are lost-- Got me stumped
Linear Mode
