how do I know where to use the “$” and lock a fomular in excel? in the link attached see the sign used in different location here:
INDEX(‘1.1 FY2016’!C$5:C$61,MATCH(‘2.1 Database’!$B61,’1.1 FY2016′!$H$5:$H$61,0))
First time, its used to separate C and 5
second time it used before B and not in between B and 61
third time its used before and in between H and 5.
Help Please
Hi Abieyuwa,
when we have C$5:C$61 we are fixing the rows. That means when we copy the formula we’re insuring we’ll remain between the rows 5 and 61, which is where our values are.
On the other hand, when we have $B61 we’re fixing the column. That means when we copy this formula we’re sure we’ll remain in the same column. Here we copy the values for code, which are all in column B.
To recap:
- If we fix before the letter, i.e. $A1, $B1, $C1 and so on, we’re fixing the column.
2. If we fix before the numbers A$1, A$2… we’re fixing the row.
3. If we fix both $A$1, we’re fixing both row and column. This ensures the value won’t change or shift position, when we copy it.
Best,
Eli