Formulas have stopped working- asking for your help to figure out why?

[wmichael]

Hello. On this spreadsheet, used to track media clippings about my organization some formulas stopped working at line 355 where I inserted copied cells. On the inserted cells I corrected the date formatting to match my formula. The inserted cells are working with some formulas but not others and I am s and the inserted cells are working with other formulas and I am at a loss to figure out why. Please see this XL2BB clip and let me know if you need more information. Thank you in advance for your help.

2019-22 Media Clips v04.xlsx
	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS
352				0	0			1						1	Lake Shelbyville to hold Life Jacket Drive	Shelbyville Daily Union (The)	N/A	N/A	SHELBYVILLE	IL	USA	4,579	$88	- Web	06-27-2019	7/3/2019	Lake Shelbyville to hold Life Jacket Drive
353				0	0									0	Sea Ray and Freedom Boat Club win National Boating Industry Safety Awards	Brunswick	N/A	N/A	LAKE FOREST	IL	USA	74,354	$6,340	- Web	12-11-2020	12/14/2020	Sea Ray and Freedom Boat Club win National Boating Industry Safety Awards
354			1	8500	404							1		1	National Boating Industry Safety Award Winners Announced by Sea Tow Foundation	Outdoor Wire (The)	N/A	N/A	GLEN ALLEN	VA	USA	1,458	$27	- Web	12-11-2020	12/14/2020	National Boating Industry Safety Award Winners Announced by Sea Tow Foundation | Outdoor Wire
355			1	0	0									1	Youre 25 Times More Likely to Die in the Winter	Bass Angler Magazine	N/A	N/A	SAN RAMON	CA	USA	8,506	$702	- Web	12-12-2020	12/14/2020	http://bassanglermag.com/youre-25-t...ure-25-times-more-likely-to-die-in-the-winter
356			1	0	0									1	Cold Weather Boating Safety Tips	Outdoor Wire (The)	N/A	N/A	GLEN ALLEN	VA	USA	1,458	$21	- Web	12-10-2020	12/14/2020	Cold Weather Boating Safety Tips | Outdoor Wire
357	1	1		0	0									1	Sea Ray and Freedom Boat Club win National Boating Industry Safety Awards	Public	N/A	N/A	NEW YORK	NY	USA	157	$407	- Web	12-11-2020	12/14/2020	Brunswick Corporation (via Public) / Sea Ray and Freedom Boat Club win National Boating Industry Safety Awards
358	1	1		0	0									1	National Boating Industry Safety Award Winners Announced by Sea Tow Foundation	Fishing Wire (The)	N/A	N/A	GLEN ALLEN	VA	USA	3,372	$55	- Web	12-11-2020	12/14/2020	<p>National Boating Industry Safety Award Winners Announced by Sea Tow Foundation</p> | Fishing Wire
359	1	1		0	0									1	1Safety at Sea - Trade Only Today	Soundings: Trade Only	N/A	N/A	ESSEX	CT	USA	3,034	$12	- Web	12-11-2020	12/14/2020	Safety at Sea
360			1	0	0									1	Cold Weather Boating Safety Tips	Fishing Wire (The)	N/A	N/A	GLEN ALLEN	VA	USA	3,372	$43	- Web	12-10-2020	12/14/2020	Cold Weather Boating Safety Tips | Fishing Wire
361	1	1		0	0									1	Sea Tow Foundation announces National Boating Industry Safety Award winners - Boating Industry	Boating Industry Online	N/A	N/A	MAPLE GROVE	MN	USA	5,890	$61	- Web	12-10-2020	12/14/2020	Sea Tow Foundation announces National Boating Industry Safety Award winners | Boating Industry
362				0	0									0	Englewood Community News Briefs for July 2, 2019	Sun Newspapers	N/A	N/A	ENGLEWOOD	FL	USA	21,268	$485	- Web	07-01-2019	7/3/2019	http://www.yoursun.com/englewood/co...cle_f9ed91da-9c15-11e9-9291-ebef04e1583c.html
363			1	0	0									1	Where to find loaner life jackets in SW FL by Safe Kids	SWFL Parent & Child	N/A	N/A	FORT MYERS	FL	USA	8,500	$404	- Web	07-02-2019	7/3/2019	http://swflparentchild.com/parenting/where-to-find-loaner-life-jackets-in-sw-fl/
364			1	0	0
raw

Cell Formulas
Range	Formula
BV352	BV352	=IF(BU352=1,CN352,"0")
BW352	BW352	=IF(BU352=1,CO352,"0")
BV353:BV364	BV353	=IF(BU353=1,CN362,"0")
BW353:BW364	BW353	=IF(BU353=1,CO362,"0")

 

[Fluff]

In BV352 you are looking at cells BU352 & CN352, but then in BV353 you are looking at cells BU353 & CN362, is that right?

 

[Candyman8019]

I would also check the values in column BU to ensure they are exactly 1, ie no odd values that may not be completely displayed.

 

[wmichael]

In BV352 you are looking at cells BU352 & CN352, but then in BV353 you are looking at cells BU353 & CN362, is that right?

No that is not correct- could that have somehow changed the formula as a result of inserting the cells?

 

[Fluff]

Probaly not, as I would have expected both references to change, unless you only inserted cells after column BU

 

[wmichael]

Probaly not, as I would have expected both references to change, unless you only inserted cells after column BU

That is exactly what I did- is there a proper way to insert cells that would prevent harm to existing formulas?

 

[Fluff]

Insert whole rows.

 

[wmichael]

Insert whole rows

Hmmm - so the inserted cells are a downloaded report that feed this spreadsheet - so then would it better to insert the cells in another spreadsheet that has as many columns then copy and past that to insert in the cells ?

 

[Fluff]

Why not just add the new data below the existing data?

 

[wmichael]

Why not just add the new data below the existing data?

When I have done that in the past I have had to update the formulas that run atop the page, which get old after awhile. Recently I did add 1000 to many of the formulas in this OL2BB clip

2019-22 Media Clips v04.xlsx
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX
1	8 Fell Marine	4 FishGilz	62 Hunter Bland	4 Formula Boats	85 Freedom Boat Club	6 Ken Cook Company	15 Kicker Audio	18 MarineMax	12 MRAA Dealer Week	6 Progressive Insurance Miami Boat Show	1 National Safe Boating Week	4 Navionics	17 New Hartford Boat Show	17 NMMA	2 Norwalk Boat Show	10 Onyx Outdoor	4 Rapid Media	9 RBFF Recreational Boating & Fishing Foundation	9 Regal Boats	1 Safe Kids	1 Sea Scouts	75 Sea Ray Boats	4 Source4	2 State of Michigan, St Clair County	1 State of New Hampshire	1 State of Florida - St Johns County	2 Swim for CJ - Dana Foland	2 Tampa Boat Show	9 Thomas H. Connolly and Sons	115 US Coast Guard	1 US Coast Guard Auxiliary Floatilla Locations	9 Wanda Kenton Smith Marketing	4 Water Sports Foundation	71 Yamaha Outboard	12 Sea Tow Locations	58 Sea Care	1 Sea Insure	2 Beacons	2 Boating Safety Eduation	3 Cold Water Immersion	65 Covid19 Issues	2 Donations	1 Engine Cut Off Device	3 Fire Prevention	4 VHF Radio	1 Passport to Safety	2 The Weather	1 Summer of Boating Safely	2 Sea Tow Foundation Website	106 Sea Tow Foundation in General	28 Flare Disposal Program	25,494,762 Flare Disposal Impressions	Flare Disposal Impressions Valued at $295,668	114 Sober Skipper Program	55,635,708 Sober Skipper Impressions	Sober Skipper Mentions Impressions Valued at $5,191,784	99 North American Sober Skipper Advisory Council	100 National Boating Industry Safety Awards	93 Life Jacket Loaner Program	26,289,047 Life Jacket Impressions	Life Jacket Impressions Valued at $3,941,307	29 Life Jacket Loaner Grant	1 Life Jacket Loaner Data Base	1 Life Jacket Drive	2 Hero Award	5 Life Jacket Selfie Giveaway Contest	6 Golden Life Jacket Award	2 WCIND Grant	1 Not STF	288 Article Saved	190 Headlines	Distributed by 177 Outlets	Across 1 Direct Media Areas	Dma Rank	Across 141 Cities	and 34 States	and 1 Countries	Produced a combined total of 106,413,294 Circulation, Impressions, and Event Attendees	With a total of $6,162,074 in Media Value	Consisting of 75 Print 287 and Web Headlines, and attendees to 0 Events that have seen our boating safety messages and break down to	Published Date	Posted Date	Web URL	2,556,797 Print Circulation	$140,239 Print Value	103,856,497 Web Impressions	$6,021,835 Web Value	0 Event Attendees
raw

Cell Formulas
Range	Formula
O1	O1	=CONCATENATE(SUM(O2:O1800)," Fell Marine")
P1	P1	=CONCATENATE(SUM(P2:P1800)," FishGilz")
Q1	Q1	=CONCATENATE(SUM(Q2:Q1800)," Hunter Bland")
R1	R1	=CONCATENATE(SUM(R2:R1800)," Formula Boats")
S1	S1	=CONCATENATE(SUM(S2:S1080), " Freedom Boat Club")
T1	T1	=CONCATENATE(SUM(T2:T1800)," Ken Cook Company")
U1	U1	=CONCATENATE(SUM(U2:U1800)," Kicker Audio")
V1	V1	=CONCATENATE(SUM(V2:V1800), " MarineMax")
W1	W1	=CONCATENATE(SUM(W2:W1800)," MRAA Dealer Week")
X1	X1	=CONCATENATE(SUM(X2:X1800), " Progressive Insurance Miami Boat Show")
Y1	Y1	=CONCATENATE(SUM(Y2:Y1800), " National Safe Boating Week ")
Z1	Z1	=CONCATENATE(SUM(Z2:Z1800), " Navionics")
AA1	AA1	=CONCATENATE(SUM(AA2:AA1800), " New Hartford Boat Show")
AB1	AB1	=CONCATENATE(SUM(AB2:AB1080)," NMMA")
AC1	AC1	=CONCATENATE(SUM(AC2:AC1080)," Norwalk Boat Show")
AD1	AD1	=CONCATENATE(SUM(AD2:AD1080)," Onyx Outdoor")
AE1	AE1	=CONCATENATE(SUM(AE2:AE1080)," Rapid Media")
AF1	AF1	=CONCATENATE(SUM(AF2:AF1800)," RBFF Recreational Boating & Fishing Foundation")
AG1	AG1	=CONCATENATE(SUM(AG2:AG1800), " Regal Boats")
AH1	AH1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AH2:AH503)>0,MATCH(AH2:AH503,AH2:AH503,0),""),IF(LEN(AH2:AH503)>0,MATCH(AH2:AH503,AH2:AH503,0),""))>0,1))," Safe Kids")
AI1	AI1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AI2:AI503)>0,MATCH(AI2:AI503,AI2:AI503,0),""),IF(LEN(AI2:AI503)>0,MATCH(AI2:AI503,AI2:AI503,0),""))>0,1))," Sea Scouts")
AJ1	AJ1	=CONCATENATE(SUM(AJ2:AJ1800), " Sea Ray Boats")
AK1	AK1	=CONCATENATE(SUM(AK2:AK1800), " Source4")
AL1	AL1	=CONCATENATE(SUM(AL2:AL1800), " State of Michigan, St Clair County")
AM1	AM1	=CONCATENATE(SUM(AM2:AM1800), " State of New Hampshire")
AN1	AN1	=CONCATENATE(SUM(AM2:AM1800)," State of Florida - St Johns County")
AO1	AO1	=CONCATENATE(SUM(AO2:AO1800)," Swim for CJ - Dana Foland")
AP1	AP1	=CONCATENATE(SUM(AP2:AP1800)," Tampa Boat Show")
AQ1	AQ1	=CONCATENATE(SUM(AQ2:AQ1800)," Thomas H. Connolly and Sons")
AR1	AR1	=CONCATENATE(SUM(AR2:AR1800)," US Coast Guard")
AS1	AS1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AS2:AS503)>0,MATCH(AS2:AS503,AS2:AS503,0),""),IF(LEN(AS2:AS503)>0,MATCH(AS2:AS503,AS2:AS503,0),""))>0,1))," US Coast Guard Auxiliary Floatilla Locations")
AT1	AT1	=CONCATENATE(SUM(AT2:AT1800)," Wanda Kenton Smith Marketing")
AU1	AU1	=CONCATENATE(SUM(AU2:AU180)," Water Sports Foundation")
AV1	AV1	=CONCATENATE(SUM(AV2:AV1800), " Yamaha Outboard ")
AW1	AW1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AW2:AW503)>0,MATCH(AW2:AW503,AW2:AW503,0),""),IF(LEN(AW2:AW503)>0,MATCH(AW2:AW503,AW2:AW503,0),""))>0,1))," Sea Tow Locations")
AX1	AX1	=CONCATENATE(SUM(AX2:AX1800)," Sea Care")
AY1	AY1	=CONCATENATE(SUM(AY2:AY1800)," Sea Insure")
AZ1	AZ1	=CONCATENATE(SUM(AZ2:AZ1800), " Beacons ")
BA1	BA1	=CONCATENATE(SUM(BA2:BA1800), " Boating Safety Eduation")
BB1	BB1	=CONCATENATE(SUM(BB2:BB1800), " Cold Water Immersion ")
BC1	BC1	=CONCATENATE(SUM(BC2:BC1800), " Covid19 Issues ")
BD1	BD1	=CONCATENATE(SUM(BD2:BD1800), " Donations")
BE1	BE1	=CONCATENATE(SUM(BE2:BE1800), " Engine Cut Off Device")
BF1	BF1	=CONCATENATE(SUM(BF2:BF1800)," Fire Prevention")
BG1	BG1	=CONCATENATE(SUM(BG2:BG1800), " VHF Radio")
BH1	BH1	=CONCATENATE(SUM(BH2:BH1800), " Passport to Safety")
BI1	BI1	=CONCATENATE(SUM(BI2:BI1800), " The Weather")
BJ1	BJ1	=CONCATENATE(SUM(BJ2:BJ1800), " Summer of Boating Safely ")
BK1	BK1	=CONCATENATE(SUM(BK2:BK1800), " Sea Tow Foundation Website ")
BL1	BL1	=CONCATENATE(SUM(BL2:BL1800), " Sea Tow Foundation in General")
BM1	BM1	=CONCATENATE(SUM(BM2:BM1800), " Flare Disposal Program")
BN1	BN1	=CONCATENATE(TEXT(SUM(BN2:BN1440),"#,##0")," Flare Disposal Impressions")
BO1	BO1	=CONCATENATE("Flare Disposal Impressions Valued at $",TEXT(SUM(BO2:BO2030),"#,##0"))
BP1	BP1	=CONCATENATE(SUM(BP2:BP1800), " Sober Skipper Program")
BQ1	BQ1	=CONCATENATE(TEXT(SUM(BQ2:BQ1440),"#,##0")," Sober Skipper Impressions")
BR1	BR1	=CONCATENATE("Sober Skipper Mentions Impressions Valued at $",TEXT(SUM(BR2:BR2030),"#,##0"))
BS1	BS1	=CONCATENATE(SUM(BS2:BS1800), " North American Sober Skipper Advisory Council")
BT1	BT1	=CONCATENATE(SUM(BT2:BT1800), " National Boating Industry Safety Awards")
BU1	BU1	=CONCATENATE(SUM(BU2:BU1800)," Life Jacket Loaner Program ")
BV1	BV1	=CONCATENATE(TEXT(SUM(BV2:BV1440),"#,##0")," Life Jacket Impressions")
BW1	BW1	=CONCATENATE("Life Jacket Impressions Valued at $",TEXT(SUM(BW2:BW2030),"#,##0"))
BX1	BX1	=CONCATENATE(SUM(BX2:BX1800), " Life Jacket Loaner Grant ")
BY1	BY1	=CONCATENATE(SUM(BY2:BY1800), " Life Jacket Loaner Data Base ")
BZ1	BZ1	=CONCATENATE(SUM(BZ2:BZ1800), " Life Jacket Drive ")
CA1	CA1	=CONCATENATE(SUM(CA2:CA1800), " Hero Award ")
CB1	CB1	=CONCATENATE(SUM(CB2:CB1800), " Life Jacket Selfie Giveaway Contest")
CC1	CC1	=CONCATENATE(SUM(CC2:CC1800), " Golden Life Jacket Award")
CD1	CD1	=CONCATENATE(SUM(CD2:CD1800), " WCIND Grant ")
CE1	CE1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(CE2:CE144)>0,MATCH(CE2:CE144,CE2:CE144,0),""),IF(LEN(CE2:CE144)>0,MATCH(CE2:CE144,CE2:CE144,0),""))>0,1))," Not STF")
CF1	CF1	=CONCATENATE(SUM(CF2:CF420)," Article Saved")
CG1	CG1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(CG2:CG512)>0,MATCH(CG2:CG512,CG2:CG512,0),""),IF(LEN(CG2:CG512)>0,MATCH(CG2:CG512,CG2:CG512,0),""))>0,1))," Headlines")
CH1	CH1	=CONCATENATE("Distributed by ",SUM(IF(FREQUENCY(IF(LEN(CH2:CH512)>0,MATCH(CH2:CH512,CH2:CH512,0),""),IF(LEN(CH2:CH512)>0,MATCH(CH2:CH512,CH2:CH512,0),""))>0,1))," Outlets")
CI1	CI1	=CONCATENATE("Across ",SUM(IF(FREQUENCY(IF(LEN(CI2:CI512)>0,MATCH(CI2:CI512,CI2:CI512,0),""),IF(LEN(CI2:CI512)>0,MATCH(CI2:CI512,CI2:CI512,0),""))>0,1))," Direct Media Areas")
CK1	CK1	=CONCATENATE("Across ",SUM(IF(FREQUENCY(IF(LEN(CK2:CK512)>0,MATCH(CK2:CK512,CK2:CK512,0),""),IF(LEN(CK2:CK512)>0,MATCH(CK2:CK512,CK2:CK512,0),""))>0,1))," Cities")
CL1	CL1	=CONCATENATE("and ",SUM(IF(FREQUENCY(IF(LEN(CL2:CL512)>0,MATCH(CL2:CL512,CL2:CL512,0),""),IF(LEN(CL2:CL512)>0,MATCH(CL2:CL512,CL2:CL512,0),""))>0,1))," States")
CM1	CM1	=CONCATENATE("and ",SUM(IF(FREQUENCY(IF(LEN(CM2:CM512)>0,MATCH(CM2:CM512,CM2:CM512,0),""),IF(LEN(CM2:CM512)>0,MATCH(CM2:CM512,CM2:CM512,0),""))>0,1))," Countries")
CN1	CN1	=CONCATENATE("Produced a combined total of ",TEXT(SUM(CN2:CN512),"#,##0")," Circulation, Impressions, and Event Attendees")
CO1	CO1	=CONCATENATE("With a total of $",TEXT(SUM(CO2:CO512),"#,##0")," in Media Value")
CP1	CP1	=CONCATENATE("Consisting of ",COUNTIF(CP2:CP512,"*Print*")," Print ",COUNTIF(CP2:CP512,"*Web*")," and Web Headlines, and attendees to ",COUNTIF(CP2:CP512,"*Event*")," Events"," that have seen our boating safety messages and break down to")
CT1	CT1	=CONCATENATE(TEXT(SUMIFS(CN2:CN512, CP2:CP512,"*Print*"),"#,##0")," Print Circulation")
CU1	CU1	=CONCATENATE("$",TEXT(SUMIFS(CO2:CO512, CP2:CP512,"*Print*"),"#,##0")," Print Value")
CV1	CV1	=CONCATENATE(TEXT(SUMIFS(CN2:CN512, CP2:CP512,"*Web*"),"#,##0")," Web Impressions")
CW1	CW1	=CONCATENATE("$",TEXT(SUMIFS(CO2:CO512, CP2:CP512,"*Web*"),"#,##0")," Web Value")
CX1	CX1	=CONCATENATE(TEXT(SUMIFS(CN2:CN512, CP2:CP512,"*Event*"),"#,##0")," Event Attendees")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI1:AI303,AI305:AI310,AW304,AI314:AI318,AI320:AI321,AI324:AI336,AI341:AI357,AI360,AI362:AI1048576	Cell Value	top 10 values	text	NO