Hi Guys,I want to add a formula to the column to the first cell, and then to be repeated for each new row, knowing that I want to disable this column because it is calculated and I do not want the user to change it. In addition, I still want the end-user to change other columns.

I am so sorry that was a problem I had at the time, I did get a result but cannot remember what it is now. I was working with files for a non-profit and no longer have access to the formula. It did work perfectly though.

If you want to lock the column reference you just need to put dollar sign before the column $=FORMULA($A2)If you want to lock the Row reference you just need to put dollar sign before the row $=FORMULA(A$2)If you want lock both just put dollar sign on both=FORMULA($A$2)

But I will have to drag it myself, it will not repeat itself and I do not how many records we might have...

If you set that section us as a table then the formula will fill the entire column and as new rows of the table are added it will add the formula accordingly.Alternatively, using dynamic arrays you can set up the formula to process on all the data. so in B1 you can use =A:A to copy all data in column A to column B. Of course the formula can get much more complicated and instead of referring to the entire column A:A you can refer to only the range that has data using something like OFFSET(A1,0,0,COUNTA(A:A)) and that could be used in a LET() statement in the cell or defined as a Name for the worksheet.

hjameelq Find a gif with some ideas using the https://support.microsoft.com/en-au/office/display-or-hide-the-fill-handle-80918200-9ae9-4615-93c9-13d4f1496f81 feature.

Adding a formula to a column and make it auto-repetitive and deactivate the column

16 Replies

Yea_So
Bronze Contributor
Sep 23, 2021
hjameelq

So this calculated column, does the user have any use for it? or its just for your own use?
Hogstad_Raadgivning
Iron Contributor
Sep 22, 2021
hjameelq
This formula will multiply column B with Column C for each row. Calculated with ROWS:

=INDEX(B2:C11;SEQUENCE(ROWS(B2:B11)),1)*INDEX(B2:C11;SEQUENCE(ROWS(B2:B11)),2)
Lock and password protect column A

excel tech.xlsx9 KB
Juliano-Petrukio
Bronze Contributor
Sep 22, 2021
If you want to lock the column reference you just need to put dollar sign before the column $
=FORMULA($A2)
If you want to lock the Row reference you just need to put dollar sign before the row $
=FORMULA(A$2)
If you want lock both just put dollar sign on both
=FORMULA($A$2)
- hjameelq
  Copper Contributor
  Sep 22, 2021
  But I will have to drag it myself, it will not repeat itself and I do not how many records we might have...
  - tabs
    Copper Contributor
    Jan 15, 2025
    Did you ever get an answer to your question? This is exactly what i'm looking to do, and every article i find either says drag, or double click. I just want the entire column filled to ∞, except for the title.

Forum Discussion

Adding a formula to a column and make it auto-repetitive and deactivate the column

16 Replies

Resources