Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
#REF! in Excel
- Thread starter Ginni
- Start date
Hi Ginni
This could be caused by a number of things, but the first 2 places you should look are;
1. Have any of the cells which show #REF! been moved recently? Have rows or columns been inserted/deleted for instance? If you have an earlier/working version of your rogue spreadsheet, fire it up and compare the R/C addresses of the misbehaving cells, then correct the cell references in the 'bad' formulas.
2. As above, but with Named Ranges. This can easily happen if a NR is on a sheet which later gets deleted. The formula on the remaining sheet(s) still refers to the defunct name; Presto #REF! If any NR's are used, but no longer exist in the workbook, you'll get #REF!'s all over the place! Try and track down any missing/old NRs and restore them or replace any reference to them with new names.
Have a look at those for now, and if it doesn't help, just post back and we'll have a delve into a couple of other possibilities!![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
Chris
Varium et mutabile semper Excel
This could be caused by a number of things, but the first 2 places you should look are;
1. Have any of the cells which show #REF! been moved recently? Have rows or columns been inserted/deleted for instance? If you have an earlier/working version of your rogue spreadsheet, fire it up and compare the R/C addresses of the misbehaving cells, then correct the cell references in the 'bad' formulas.
2. As above, but with Named Ranges. This can easily happen if a NR is on a sheet which later gets deleted. The formula on the remaining sheet(s) still refers to the defunct name; Presto #REF! If any NR's are used, but no longer exist in the workbook, you'll get #REF!'s all over the place! Try and track down any missing/old NRs and restore them or replace any reference to them with new names.
Have a look at those for now, and if it doesn't help, just post back and we'll have a delve into a couple of other possibilities!
Chris
Varium et mutabile semper Excel
- Status
Similar threads
- Question