Turn off Auto fill in table column

[HebertsYES]

I Have 'formatted as a table' in order to take advantage of the cell references, have total row, allow for easy insert/delete rows inside the table, etc. Problem is if someone enters a formula in one of the intended [FONT=inherit !important][FONT=inherit !important]data [/FONT][FONT=inherit !important]entry[/FONT][/FONT] cells, it autofills that formula down the entire column in the table.

Is there a way to disable that? Of course, if so, doing so might [FONT=inherit !important][FONT=inherit !important]yield[/FONT][/FONT] a result I don't want on other columns, so it'd be nice if this was doable on a column by column basis, but that's probably hoping for too much?

 

[alexeymolchanov]

Hello,

1. To generally deactive the autofill of the formulas use the following path (in Excel 2010):
File - Options - Proofing - AutoCorrect Options... - AutoFormat As You Type
... and deactivate "Fill Formulas in tables to create calculated columns"

2. To make this option active or inactive depending on a column in the table, a macro is needed.

Generally, this option is accessible as follows:

Application.AutoCorrect.AutoFillFormulasInLists = [COLOR=#0000cd]True
[/COLOR]Application.AutoCorrect.AutoFillFormulasInLists = [COLOR=#0000cd]False[/COLOR][COLOR=#0000cd]
[/COLOR]

...for activated or deactivated autofill respectively.

Let's assume there is an Excel table named "Test" with four columns, two data rows and one total row:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Column 1[/TD]
[TD="align: center"]Column 2[/TD]
[TD="align: center"]Sum_Manual[/TD]
[TD="align: center"]Sum_Autofill[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

where in column Sum_Manual the autofill option should be deactivated and in column Sum_Autofill the option should be activated.

Here is a possible macro which will activate or deactive the autofill option for formulas depending on where user stands.

[COLOR=#0000cd]Private Sub[/COLOR] Worksheet_SelectionChange([COLOR=#0000cd]ByVal[/COLOR] Target [COLOR=#0000cd]As[/COLOR] Range)

[COLOR=#0000cd]Dim[/COLOR] ColumnDataCellRange, ColumnFirstCellAddress, ColumnLastCellAddress [COLOR=#0000cd]As String[/COLOR]

ColumnDataCellRange = Range("Test[Sum_Manual]").Address([COLOR=#0000cd]False[/COLOR], [COLOR=#0000cd]False[/COLOR])
ColumnFirstCellAddress = Left(ColumnDataCellRange, InStr(ColumnDataCellRange, ":") - 1)
ColumnLastCellAddress = Right(ColumnDataCellRange, Len(ColumnDataCellRange) - InStr(ColumnDataCellRange, ":"))

[COLOR=#0000cd]If[/COLOR] ActiveCell.Address([COLOR=#0000cd]False[/COLOR], [COLOR=#0000cd]False[/COLOR]) >= ColumnFirstCellAddress _
[COLOR=#0000cd]And[/COLOR] ActiveCell.Address([COLOR=#0000cd]False[/COLOR], [COLOR=#0000cd]False[/COLOR]) <= ColumnLastCellAddress [COLOR=#0000cd]Then[/COLOR]

Application.AutoCorrect.AutoFillFormulasInLists = [COLOR=#0000cd]False[/COLOR]

[COLOR=#0000cd]Else[/COLOR]

Application.AutoCorrect.AutoFillFormulasInLists = [COLOR=#0000cd]True[/COLOR]

[COLOR=#0000cd]End If[/COLOR]

[COLOR=#0000cd]End Sub[/COLOR]

1. The macro should be added to the worksheet where the table is located and starts on selection of any cell in the worksheet
2. The variables are used to get the name of data rows of column Sum_Manual in a form like "C2:C3" which is then split into the first data cell "C2" and last data cell "C3".
3. If the user selects a cell with address not smaller than "C2" and not bigger than "C3", then the autofill option is deactivated.
As a result, if the user enters a formula in a data cell of column Sum_Manual, it will not be autofilled down.
4. If the user selects a cell outside of the range "C2:C3", then the autofill option is activated.
As a result, if the user enters a formula in a data cell of column Sum_Autofill, it will be autofilled down.

In this way you can define for each column in the table whether the autofill option should be activated or deactivated.