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

back

Follow this topic

Abieyuwa O-Onaeko

Last answered:

April 01, 2020

Posted on:

March 31, 2020

using the “$” to lock a fomular

1 answers ( 0 marked as helpful)

Elitsa Kaloyanova

Posted on:

April 01, 2020

Submit an answer

Content