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

%3CLINGO-SUB%20id%3D%22lingo-sub-2774114%22%20slang%3D%22en-US%22%3EAdding%20a%20formula%20to%20a%20column%20and%20make%20it%20auto-repetitive%20and%20deactivate%20the%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2774114%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3EI%20want%20to%20add%20a%20formula%20to%20the%20column%20to%20the%20first%20cell%2C%20and%20then%20to%20be%20repeated%20for%20each%20new%20row%2C%20knowing%20that%20I%20want%20to%20disable%20this%20column%20because%20it%20is%20calculated%20and%20I%20do%20not%20want%20the%20user%20to%20change%20it.%20In%20addition%2C%20I%20still%20want%20the%20end-user%20to%20change%20other%20columns.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2774114%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2774128%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20a%20formula%20to%20a%20column%20and%20make%20it%20auto-repetitive%20and%20deactivate%20the%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2774128%22%20slang%3D%22en-US%22%3EIf%20you%20want%20to%20lock%20the%20column%20reference%20you%20just%20need%20to%20put%20dollar%20sign%20before%20the%20column%20%24%3CBR%20%2F%3E%3DFORMULA(%24A2)%3CBR%20%2F%3EIf%20you%20want%20to%20lock%20the%20Row%20reference%20you%20just%20need%20to%20put%20dollar%20sign%20before%20the%20row%20%24%3CBR%20%2F%3E%3DFORMULA(A%242)%3CBR%20%2F%3EIf%20you%20want%20lock%20both%20just%20put%20dollar%20sign%20on%20both%3CBR%20%2F%3E%3DFORMULA(%24A%242)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2774187%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20a%20formula%20to%20a%20column%20and%20make%20it%20auto-repetitive%20and%20deactivate%20the%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2774187%22%20slang%3D%22en-US%22%3EIf%20you%20set%20that%20section%20us%20as%20a%20table%20then%20the%20formula%20will%20fill%20the%20entire%20column%20and%20as%20new%20rows%20of%20the%20table%20are%20added%20it%20will%20add%20the%20formula%20accordingly.%3CBR%20%2F%3EAlternatively%2C%20using%20dynamic%20arrays%20you%20can%20set%20up%20the%20formula%20to%20process%20on%20all%20the%20data.%20so%20in%20B1%20you%20can%20use%20%3DA%3AA%20to%20copy%20all%20data%20in%20column%20A%20to%20column%20B.%20Of%20course%20the%20formula%20can%20get%20much%20more%20complicated%20and%20instead%20of%20referring%20to%20the%20entire%20column%20A%3AA%20you%20can%20refer%20to%20only%20the%20range%20that%20has%20data%20using%20something%20like%20OFFSET(A1%2C0%2C0%2CCOUNTA(A%3AA))%20and%20that%20could%20be%20used%20in%20a%20LET()%20statement%20in%20the%20cell%20or%20defined%20as%20a%20Name%20for%20the%20worksheet.%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 

9 Replies
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 "fill handle" feature.

 

Autofill.gif

I need to add a formula to a column and make it disabled, not a data validation. A formula on the column and I want it to calculate the values in disable mode as I do not want users to change or enter anything there.
I understand except I don't think you mean or at least I don't understand what you mean by 'disabled'. I'm assuming you mean locked so the used can't edit that cell/column.
To do that you should highlight the cells/columns that the user is allowed to edit, right click and go to format cells, and under protect uncheck 'Locked' so those cells are NOT locked and then under the Review tab select Protect Sheet and you can select from various option and choose to give a password or not. This will prevent the user from editing that column or any other columns that you left 'Locked'.
As for automatically expanding the formula into additional cells I addressed above.
I recommend the 1st option with making a table. Highlight the area with the data and go to the home tab and select 'Format as Table' under 'Styles'. The make sure the range is correct if the first row is headers or not. Then when it is formatted as a table, when the user starts a new row, Excel will automatically expand the table and copy any formula accordingly.

@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

 

 

@hjameelq 

 

So this calculated column, does the user have any use for it? or its just for your own use?