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

[BenGrobler]

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?

 

[Joe4]

Where exactly are these new columns being inserted?
Are you sure that is how it is updating the formula? It seems odd that it is updating the starting the cell (O4), but not the ending cell (XFD4). Usually, both would be updated if you insert a nwe column.

It may be helpful to see a small data sample.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[BenGrobler]

Where exactly are these new columns being inserted?
Are you sure that is how it is updating the formula? It seems odd that it is updating the starting the cell (O4), but not the ending cell (XFD4). Usually, both would be updated if you insert a nwe column.

It may be helpful to see a small data sample.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Attendance List Trends.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	SA Metal ELF Employees														00 January 1900
2	COY Number	Employee Name & Surname	Awop	Sick	Late	Annual Leave	Fam Resp Leave	Long Service Leave	Unpaid Leave	WCA	Suspended	Dismissed	Resigned
3
4	902	Andries Sibiya	0	0	0	0	0	0	0	0	0	0	0
5	903	Frans Bila	0	0	0	0	0	0	0	0	0	0	0
6	909	Mario Machava	0	0	0	0	0	0	0	0	0	0	0
7	910	Kheti Kanye	0	0	0	0	0	0	0	0	0	0	0
8	913	Jaime Mausse	0	0	0	0	0	0	0	0	0	0	0
9	916	Jeremiah Nkosi	0	0	0	0	0	0	0	0	0	0	0
10	952	Bongi Shelembe	0	0	0	0	0	0	0	0	0	0	0
11	954	Mxosheni Nsibande	0	0	0	0	0	0	0	0	0	0	0
12	2507	Life Khubayi	0	0	0	0	0	0	0	0	0	0	0
13	2806	Moleke Malepe	0	0	0	0	0	0	0	0	0	0	0
Trend

Cell Formulas
Range	Formula
O1	O1	='SAM ELF YARD ATTENDENCE'!M2
C4:C13	C4	=COUNTIF(P4:XFD4,"AWOP")
D4:D13	D4	=COUNTIF(P4:XFD4,"Sick")
E4:E13	E4	=COUNTIF(P4:XFD4,"Late")
F4:F13	F4	=COUNTIF(P4:XFD4,"Annual Leave")
G4:G13	G4	=COUNTIF(P4:XFD4,"Fam Resp Leave")
H4:H13	H4	=COUNTIF(P4:XFD4,"Long Service Leave")
I4:I13	I4	=COUNTIF(P4:XFD4,"Unpaid Leave")
J4:J13	J4	=COUNTIF(P4:XFD4,"WCA")
K4:K13	K4	=COUNTIF(P4:XFD4,"Suspended")
L4:L13	L4	=COUNTIF(P4:XFD4,"Dismissed")
M4:M13	M4	=COUNTIF(P4:XFD4,"Resigned")

 

[Joe4]

Thanks for posting a data sample, but you did not answer either question that I asked.

 

[RoryA]

It looks like you could just start your formula at column N, since it's blank, then inserting new columns before column O won't be a problem.

 

[BenGrobler]

Thanks for posting a data sample, but you did not answer either question that I ask

the data is entered in column N and a new column is inserted. N becomes O, the formulas start in O

 

[Joe4]

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.

 

[BenGrobler]

It looks like you could just start your formula at column N, since it's blank, then inserting new columns before column O won't be a problem.

that does not work when the new column is inserted the formula then starts with O, insert again and the formula starts with P

 

[BenGrobler]

I need the formula to reset itself back to column O or N with a vba code or something? Any Idea?

 

[RoryA]

that does not work when the new column is inserted the formula then starts with O, insert again and the formula starts with P

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.