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

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
31
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?
 
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
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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....
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top