deleting data gives a #ref

janus67

Board Regular
Joined
Nov 26, 2006
Messages
143
Office Version
  1. 2016
Platform
  1. Windows
I have an excel 2007 file which over the years has grown to ~ 20 yrs of input data -- which is unnecessary now
so I deleted about 5+ years of input data ( dates & numbers) and assumed that Excel would restart from the oldest number now

not true -- all output columns are filled with #ref

how can I get rid of the older data and have Excel recalculate from the new "first " date?
without rebuilding the sheet from scratch
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,183
Office Version
  1. 2016
Platform
  1. Windows
The #REF arises because you have formulas that refer to cells/columns/rows that have been deleted.
Before deleting, you need to review your formulas and make appropriate adjustments.
 

janus67

Board Regular
Joined
Nov 26, 2006
Messages
143
Office Version
  1. 2016
Platform
  1. Windows
The #REF arises because you have formulas that refer to cells/columns/rows that have been deleted.
Before deleting, you need to review your formulas and make appropriate adjustments.

Hi footoo, I thought that when I copy/pasted the formula from say Col1 throught to col 100 , that copied the formula into each cell--apparently not
when you say " appropriate adjustments" do you mean that I have to reintroduce the original formula to say Col1 & then copy again etc for each cell/page

I can do it for one of my sheets ( my own ) but another one has proprietary locked formulas that I can't do it with -- but I can just start up another copy with the shorter data anyway
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,183
Office Version
  1. 2016
Platform
  1. Windows
Without knowing what your data looks like or what formulas are involved, I am not able to make suggestions.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top