Convert Cell References UtilityConvert one or all the formulas on a spreadsheet in a single operation to:
- A1 Format
- $A1 Format
- A$1 Format
- $A$1 Format
- Add the active Sheet Name to the formulas
- Remove the active Sheet Name from the formulas
This utility allows the user to convert the cell references of the formulas within the selected range of cells to any form of absolute reference. This can be useful in situations where you have two or more sheets linked together via formula functions. Keep in mind that you can do a compounded conversion by running this routine multiple times on the same set of cells to achieve the desired results. Convert some or all of the cells to and from A1, $A1, A$1, $A$1, or add or delete the sheet name to and from the formulas.
Note: To view all the formulas on a sheet at once, press the Control and ` keys at the same time. Repeat the keystrokes to toggle back to the previous view.
When installed, a new menu item is added to the Tools menu called “Convert Cell References”.
1. If you need to have two synchronized sheets linked together, in this case the first sheet contains values, and the second sheet contains formula functions based on the first sheet; and you want or need the capability to sort the first sheet and have the second sheet updated correctly, then you need to convert the formula references on the second sheet to $A$1 format. If you don't convert the references to $A$1 format, sorting the first sheet will cause the formula references on the second sheet to refer the location the original cells independent on the contents of referenced cells.
2. If you plan to do any copying, cutting, and pasting between two sheets and need to retain the formula links between the sheets, then you will need to add the sheet name to the formulas in the source sheet prior to copying, cutting, and pasting. After adding the sheet names to the formulas, you may also need to convert the formulas a second time to $A$1 format.
Installation and Usage:
To have this utility accessible every time you start Excel, do the following, otherwise just load the file manually when needed using the File-Open command.
1. Copy the cellrefconvert.xls file to your Excel's XLStart Directory.
2. When you open Excel, the Tools menu will contain a new command called: "Convert Cell References". That's It !
Dialog Box User Interface:
Caution: If you choose to add or remove the sheet names from formulas, then do not switch spreadsheet or workbook windows when you are prompted to select the range of cells. Both the add and remove sheet names routines will only function correctly on the cells of the active sheet that you initially launched the utility from (via the Tools menu).