03-14-2015, 10:37 AM
Alex, what you're referring to is called a 'fixed' or 'direct reference'.
By default, spreadsheets use 'relative reference' wherein when you copy a formula, all the referenced cells in the original formula cell are adjusted by the same number of rows and columns of movement to the target cell. So, if the source cell formula is located in G5 is "A2+B3" and you copy the formula to H7, the resultant formula with relative references would be "B4+C5". Using your '$' before the row and column numbers in the formula will keep the reference (cells pointed at) fixed.
You can also use fixed references to your advantage when referring to a range of values. If you want to keep the column fixed but let the row vary (such as a formula to be used across a portfolio range) just put the '$' before the letter (column) but not before the number (row). For example:
Note that the column names stayed the same as E2 (the source formula) but the row varied with the row copied to. What you have to watch out for is the case of E5 & F5. The formula is the same. Copying the formula row-wise will give you the same results.
Likewise, you can do the same thing with rows being fixed and the column varying by putting the '$' before the number but NOT before the letter. For example:
In both cases, you want to watch out for referenced cells going out of range. In the 2nd example, the columns referenced changed to 'D' & 'E' but there is no data in them.
Hope that helps.
By default, spreadsheets use 'relative reference' wherein when you copy a formula, all the referenced cells in the original formula cell are adjusted by the same number of rows and columns of movement to the target cell. So, if the source cell formula is located in G5 is "A2+B3" and you copy the formula to H7, the resultant formula with relative references would be "B4+C5". Using your '$' before the row and column numbers in the formula will keep the reference (cells pointed at) fixed.
You can also use fixed references to your advantage when referring to a range of values. If you want to keep the column fixed but let the row vary (such as a formula to be used across a portfolio range) just put the '$' before the letter (column) but not before the number (row). For example:
Note that the column names stayed the same as E2 (the source formula) but the row varied with the row copied to. What you have to watch out for is the case of E5 & F5. The formula is the same. Copying the formula row-wise will give you the same results.
Likewise, you can do the same thing with rows being fixed and the column varying by putting the '$' before the number but NOT before the letter. For example:
In both cases, you want to watch out for referenced cells going out of range. In the 2nd example, the columns referenced changed to 'D' & 'E' but there is no data in them.
Hope that helps.
=====
“While the dividend itself is merely a rearrangement of equity, over time it's more like owning an apple tree. The tree grows the apples back again and again and again, and the theoretical value of the tree doesn't change just because of when the apples are about to fall.” - earthtodan
“While the dividend itself is merely a rearrangement of equity, over time it's more like owning an apple tree. The tree grows the apples back again and again and again, and the theoretical value of the tree doesn't change just because of when the apples are about to fall.” - earthtodan