If you have any experience working with Microsoft Excel formulas, then you already know that they can be tricky beasts.  I find this to be especially a problem when I cut and paste formulas.  While this is good most of the time, as the formula will change based on the location of where you pasted it to relative to it’s original location, what if you want to only have some of the references change?

This is where Absolute Cell References come into play.  Here is an example situation:  You have created a workbook to track the results of a survey that is being given out.  You have a spreadsheet (let’s call it “backend”) to hold the questions themselves and the associated point value for each answer.  The other spreadsheet (”frontend”) has the results for each survey filled out, showing the answer and the associated point value.  This is done through a formula similar to the one shown below:

=IF(a1=”a”,backend!c1,IF(a1=”b”,backend!c2,”Not Valid”));

This will auto-populate a cell based on the value of a1.  If “a” is entered, the value will be the value of cell c1 in the “backend” spreadsheet.  If it is “b”, the value will be the value of cell c2 in the “backend” spreadsheet.  Otherwise, the answer is marked “Not Valid”.  This seems simple enough and works for one survey result.  However, what happens when you want to fill out the results of another survey?  You can cut and paste the formula, however the cell references for the answer point values (in “backend”) will change as well as the new input cells.  What we want here is the “frontend” reference to change based on location (so “a1″ would change) but the “backend” values to stay the same (so we score each survey the same for each question).

To accomplish this, all we have to do is make the “backend” reference an Absolute Cell Reference.  This is easily accomplished below:

=IF(a1=”a”,backend!$c$1,IF(a1=”b”,backend!$c$2,”Not Valid”));

By adding the dollar signs to the backend cell references, these cell references will not change when the formula is cut and pasted to another row.  Only the “frontend” references will change.  This allows multiple survey results to be inputted into “frontend” without having to change the formulas each time to account for the new location.