Formulas not retaining cell information

I have created formulas on a sheet where I use code to poplulate a form.  I noticed when I delete the forms where I paste the data to I get #Ref! in the formula that is to be used to paste into the sheet.  Is there a way to not lose the cell references in the formulat when data is deleted?

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
You will only get #ref errors of you delete cells. Clearing them will not give you that error.
You can use INDIRECT:


Where "Address" equals the original range addresses.

leezacAuthor Commented:
Thanks but don't understand.....

This is how it should look


I have a formula page and was going to use vba to add to columns on CPU sheet, but when  I delete the CPU sheet I the the REF! error on the formula tab
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

SmittyProConnect With a Mentor Commented:


Note that the range references become static when you use INDIRECT, so to make D4 dynamic you'd need to do some workarounds with ADDRESS, COLUMN & ROW.  



Another issue is that while INDIRECT is incredibly handy, it can also get very expensive with regards to overhead if you have a lot of them.
Rory ArchibaldCommented:
Don't delete the cells - clear them instead.
leezacAuthor Commented:
Rorya,  I tried clear and go the #ref! error....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.