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

[James Perry]

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....????

 

[NARAYANK991]

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

 

[vijay.vizzu]

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...

 

[James Perry]

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.

 

[Somendra Misra]

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,

 

[James Perry]

Thanks, Som.