Have you ever spent hours developing a spreadsheet with complex formulas and cross-sheet references and find that you need to add a column or row?
So, you decide to add the column or row and now all of your formulas no longer work. Why? The formulas were most probably written with standard (A1:E12) or absolute ($A$1:$E$12) references. This means that your formulas are looking for a very specific location in your spreadsheet for the data it needs to successfully complete the calculation. Often, adding a column or row will change the actual size of the data being referred to and when your formulas are looking for the data it may not find it because the data has moved. The best way to resolve this is by using Named Ranges.
A Named Range is a name given to select cell or range of cells that contain data. When you move a cell that has been assigned a named range, the spreadsheet will keep track of the location of the defined range of data automatically. There are certain caveats; however, when you need to make modifications to your layout or formulas, you only have to update the named range. For instance, in the picture above, cell range A1 thru E12 has been assigned a named range of “RegionalSales”. You can refer to this name in your formulas or VBA without having to retype the actual range reference, Example (VLOOKUP(RegionSelect, RegionalSales, 2, FALSE) will result in the formula matching the cell reference (RegionSelect or F1) with the Region column and return the data in the 2nd column associated with the matched reference ($10,345.00). There are many more applications for this method.
The possibilities are endless.
