How to solve the #REF! Error on spreadsheets
Can there be anything more annoying than getting #REF! error in the cells of your spreadsheet?
The biggest problem Excel users face when trying to share files for reporting purposes is the fact that, unless those files are always in the same place and on the same network, inevitably you will lose the cell references (#REF!) for many of your formulas sooner or later, and this will basically destroy all the effort that has gone into making the sheet in the first place.
So, if you have people outside of your network, you cannot simply share a file because the references will not be the same. People, therefore, resort to sending the file, which will then be copied and pasted into another file….often overwriting older data and so forth. As we know, this is DANGEROUS. All too often we use the wrong file, or an older version, or we paste into the wrong place etc….. It’s a recipe for disaster!!!
There are 2 main ways to get the #REF! error, the most common is
Losing the reference of a particular cell
Write a sum like =SUM(A1:A10, B1:B10, C1:C10) and then delete the column C. Immediately the sum formula returns #REF! Error.
Probably the most common error messages when working with a worksheet full of formulas. You get #REF! error when one of the formula parameters is pointing to an invalid range. This can happen because you deleted the cells.
Not much can be done about this other than adjusting the formula or undoing what you did.
Losing the reference to a Workbook
This happens when a workbook is moved out of the environment it was created in, for example you leave the office for a business trip. You are no longer on the network, and hence all your references are lost. Should you want to complete a Expense report using a XLS template, you will need to physically send it in to be processed, which ultimately means cutting and pasting.
Here’s a breakdown of the reference parts:
- File Path: It points to the drive and directory (be it local or network) in which your Excel file is stored (C:\Sales\ in this example).
- Workbook Name: It includes the file extension (.xlsx, .xls, or .xslm) and is always enclosed in square brackets, like [SalesYTD.xlsx] in the above formula.
- Sheet Name: This part of the Excel external reference includes the sheet name followed by an exclamation point where the referenced cell(s) is located (Jan! in this example).
- Cell Reference: It points to the actual cell or a range of cells referenced in your formula.
What’s the solution to workbook referencing problems?
Basically, the solution is CLOUD!
Using Google Sheets and the ImportSheet Add-on, you can link your files anyhow and anywhere, no matter where the other file may be, so long as it is stored in Google DRIVE.
Because the file is located in the DRIVE, independent of whose drive it is, it is always located and hence, so long as it has been shared with you, you no longer need to worry about copying and pasting, or sending outdated files.
If you are at the airport and want to update your expense report whilst waiting you can. The person who consolidates expenses will automatically receive your inputs the moment they update their spreadsheet, without copying or pasting.
You can connect to other workbooks, you can append other workbooks, you can consolidate data from other workbooks. You can filter, you can query, or import a specific range from a sheet in another workbook which may be owned by someone on the other side of the planet.