• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to 'auto-lock' a formula with a $ symbol...........

A formula repeats itself 26 times in cells A1 thru' Z1 by dragging across
or paste special.
Values are in cells A2 thru' Z2.
Now I lock row 1 in col A with the $ sign.
The formula now reads…......A$1 (instead of A1).
But I would also like the $ sign to 'auto-appear' in the remaining
25 cells, B1 thru Z1.
What Excel formula can do the same....????
 
Last edited:
Hi James ,

Instead of explaining , always try to illustrate with an example.

1. What is the formula in A1 ?

2. What do you want the formula to be in B1 , C1 ,..., Z1 ?

3. What is the connection between the formulae in row 1 ( A1 through Z1 ) with the values in row 2 ( A2 through Z2 ) ?

Narayan
 
Dear James..

I will prefer. to have a good understanding regarding Absolute & Relative references. They will play vital role while applying formulas in other cells/ranges.

As you mentioned above... A1 became A$1 (That means you make Row Absolute and column relative reference) if you tried this in excel sheet.. what ever the value in A1 will repeated in below cells (if you drag vertically, down wards) becoz you freezed the Row no by putting $1, but if you drag the formula horizontally, it will show the B column values, C column values...so on...

So please make sure that, what is your requirement ??. you can watch the changes by changing the references by pressing F4 key, it will toggle the references.

I hope you got my point... :)
 
Hello guys.....
Sorry about the incompleteness.
Here's one solution.....
Select the cells, replace "1" with "$1" with the help of "Find & Replace".
Thank you for the interest shown.
 
Hi James,

If you just Click on cell A1, press F2 and click F4 key twice you will get =A$1. Now just drag this till the end you will get all formula replaced.

Regards,
 
Back
Top