How to keep Formulas in excel from moving with columns when inserting.

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have a formula that starts in Column"O" , each time i process data that is captured in Column "O" then a new column is inserted for the next data.
My Formula is =COUNTIF(O4:XFD4,"Cool") after inserting the new column my formula is =COUNTIF(P4:XFD4,"Cool")

i need it to stay the same after inserting a new column?
 

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
One trick would be to insert a blank column at column N, then hide it, and use that as the start column in your COUNTIF formulas.
Then insert all new columns starting at column O.
i hear you but i dont understand how would i instert a blank column if it is already a blank column being inserted
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You hadn't mentioned that the inserting was happening at the column I suggested using as the anchor. I'd add a blank separator column and hide it. Then use that as the anchor.
ok, how would i do that use a column as an anchor?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
i hear you but i dont understand how would i instert a blank column if it is already a blank column being inserted
Who or what controls exactly where the new blank columns are to be inserted?
Is that "set it stone" or can that be changed?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Insert a column before column N now. Hide it. Then start your formulas at column N. Since you'll now be inserting new columns from column O, the formulas will automatically include them.
 

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Who or what controls exactly where the new blank columns are to be inserted?
Is that "set it stone" or can that be changed?
let me post the first mini sheet then you can see
Attendance List Trends.xlsm
ABCDEFGHIJK
1SAM ELF YARD ATTENDED22 April 2021
2DATE:22-04-2021SHIFT: DAY
3Clock No:Employee NameReason for absence - give detailManager Date of AbsenceLeave applicableExpected return dateWas the absence reported? To which manager?Further Comments
4903Frans Bila Awop
5#N/A
6#N/A
7#N/A
8#N/A
9#N/A
10#N/A
11#N/A
12#N/A
13#N/A
14#N/A
15#N/A
16#N/A
17903
SAM ELF YARD ATTENDENCE
Cell Formulas
RangeFormula
C1C1='SAM ELF YARD ATTENDENCE'!B2
B2B2=TODAY()
B4:B16B4=LOOKUP(A4,Sheet1!A:A,Sheet1!B:B)
A17A17='SAM ELF YARD ATTENDENCE'!A4
Cells with Data Validation
CellAllowCriteria
H4:H16List=Sheet1!$D$29:$D$33
C4:C16List=Sheet1!$D$1:$D$14
D4:D16List=Sheet1!$D$16:$D$20
E4:E16Datebetween 01-01-2021 and 31-12-2099
F4:F16List=Sheet1!$D$22:$D$27
 

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
when i add data in the first sheet then press the process button is copies the data and places it on the second sheet next to the name on the first sheet.
i want to be able to count the number of "awop", "leave" etc etc etc. this is why there is a count if formula in there. So now after a while i can pick up a trend of some of the employees like who is awop on a friday or monday and how many times. or who is sick on a monday or friday and how many times....
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

when i add data in the first sheet then press the process button
What does the code behind this button look like?
 

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What does the code behind this button look like?
VBA Code:
Sub Button2_Click()
Dim R As Range, v As Variant
For Each R In Range("A4:A16").Cells
    v = Application.Match(CStr(R.Value), Array("902", "903", "909", "910", "913", "916", "952", "954", _
        "2507", "2806", "2831", "2842", "2917", "2954", "2967", "3059", "3079", "3099", "3183", _
        "3270", "3271", "3284", "3328", "3362", "3450", "3507", "3567", "3603", "3631", "3632", _
        "3635", "3639", "3643", "3646", "3721", "3782", "3813", "3829", "3836", "3841", _
        "3913", "3941", "3973", "3976", "3980", "3997", "4052", "4053", "4059", "4062", "4087", _
        "4089", "4090", "4091", "4092", "4104", "4105", "4106", "4108", "4119", "4120", "3655", "20401", "20803", "20807", "20939"), 0)
    If Not IsError(v) Then Sheets("Trend").Range("n" & v + 3).Value = R.Offset(, 2).Value
Next R
    Sheets("trend").Select
    Range("n1").Select
    ActiveCell.FormulaR1C1 = "='SAM ELF YARD ATTENDENCE'!R[1]C[-1]"
    Range("n1").Select
    Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"
    Columns("n:n").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("o:o").EntireColumn.AutoFit
    Range("o15").Select
    Sheets("SAM ELF YARD ATTENDENCE").Select
    Range("J14").Select
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
OK, that code is telling it where to insert rows, right here:
VBA Code:
    Columns("n:n").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
So you can easily insert the blank anchor column like Rory and I suggested, and change this code to start everything one column over (i.e. insert at column O instead of column N).

Also note, you can get rid of a lot of the "Select/Selection" statements in your code. This happens when you use the Macro Recorder (the Macro Recorder is a great tool, but is very literal and often a little code "clean-up" is recommended after using it). It is usually not necessary to select a range to work with it. Removing them will shorten your code, make it "cleaner" to read, and make it run faster.

So, blocks of code like this:
VBA Code:
    Range("n1").Select
    ActiveCell.FormulaR1C1 = "='SAM ELF YARD ATTENDENCE'!R[1]C[-1]"
can be simplified to this:
VBA Code:
    Range("n1").FormulaR1C1 = "='SAM ELF YARD ATTENDENCE'!R[1]C[-1]"

Likewise, this:
VBA Code:
    Columns("n:n").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
could be simplified to this:
VBA Code:
    Columns("n:n").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Most of the time, anytime where one line ends in "Select" and the next begins with "Selection" or "ActiveCell", they can be combined.
 

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
OK, that code is telling it where to insert rows, right here:
VBA Code:
    Columns("n:n").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
So you can easily insert the blank anchor column like Rory and I suggested, and change this code to start everything one column over (i.e. insert at column O instead of column N).

Also note, you can get rid of a lot of the "Select/Selection" statements in your code. This happens when you use the Macro Recorder (the Macro Recorder is a great tool, but is very literal and often a little code "clean-up" is recommended after using it). It is usually not necessary to select a range to work with it. Removing them will shorten your code, make it "cleaner" to read, and make it run faster.

So, blocks of code like this:
VBA Code:
    Range("n1").Select
    ActiveCell.FormulaR1C1 = "='SAM ELF YARD ATTENDENCE'!R[1]C[-1]"
can be simplified to this:
VBA Code:
    Range("n1").FormulaR1C1 = "='SAM ELF YARD ATTENDENCE'!R[1]C[-1]"

Likewise, this:
VBA Code:
    Columns("n:n").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
could be simplified to this:
VBA Code:
    Columns("n:n").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Most of the time, anytime where one line ends in "Select" and the next begins with "Selection" or "ActiveCell", they can be combined.
I have no idea what an anhor column is i am very new to this, please explain how to do this? please please
 

Forum statistics

Threads
1,136,275
Messages
5,674,779
Members
419,524
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top