Stumped on my Summary Sheet

[jthomas3029]

I copied a Mr. Excel VBA formula, Copy Wrksheet range in VBA consolidation. It has worked well until I added more data into the rows on my worksheets. The individual worksheets are displaying the correct calculations but the summary sheet does not. The summary sheet draws upon a formula from a previous row within the summary sheet even though it reflects the correct worksheet formula. To add to it the first worksheet is always correct on the summary sheet no matter how many new rows of data I add.

 

[jthomas3029]

I created one master sheet with all the formulas and then did a copy for all the others.; however, I did just now find one at the tail end list as "Sheet 1". I removed it did a save and reran the macro but alas it did not work.

 

[jthomas3029]

No...the unassigned sheets do not have data in them except for the headers. I deleted all of them and saved and ran the file again with no change.

 

[MARK858]

Can you please upload your workbook to a free file hosting site like www.box.com or www.dropbox.com, mark the file for sharing then post the link it provides in the thread.

Make sure that up alter any sensitive data before uploading the file.

 

[jthomas3029]

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[MARK858]

Ok, just so that we are singing from the same song sheet below are the results that I am getting with the macro in the workbook (no errors btw, although you could do with setting events to false).

Can you with results below explain to me which parts are incorrect and why please?

Scoping 2020 7.2 (1).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Scoping 2020
2
3
4	Laura Collis
5	Assignment					Dates/Recipient						Billing
6	Title of Job	Type of Proceeding & Service Level	Rate	Pages		Rec'd	Due	Status	Recipient	Sent		Total Charged CDN	Proposed Billing Send Date	Cycle	Status	Actual Send Date	Rec'd Date	Amount Rec'd USD
7	D20JAN20LC (Drinovac)	25-Line Exam-R	$1.25	61		22/01/2020	24/01/2020	Completed	Reporter	24/01/2020		$76.25	31/01/2020	Jan End	Completed	########	#########	$54.51
8	D06FEB20LC (Pezeshkzad)	25-Line Exam-R	$1.25	94		07/02/2020	12/02/2020	Completed	Reporter	11/02/2020		$117.50	29/02/2020	Feb End	Completed	########	#########
9	D13FEB20LC Lovan	25-Line Exam-R	$1.25	81		14/02/2020	21/02/2020	Completed	Reporter	21/02/2020		$101.25	29/02/2020	Feb End	Completed	########	#########
10	D10OCT19LC (Grewal)	25-Line Exam-R	$1.25	71		20/03/2020	28/03/2020	Completed	Reporter	26/03/2020		$88.75	15/04/2020	Mid Apr	Completed	########	#########
11	XXXXXXX	25-Line Hearing-R	$1.40	60		14/06/2020	17/06/2020	Completed	Reporter	19/06/2020		$84.00	30/06/2020	June End	Completed
12
13
14	Sorrell-Ann Cadsky
15	Assignment					Dates/Recipient						Billing
16	Title of Job	Type of Proceeding & Service Level	Rate	Pages		Rec'd	Due	Status	Recipient	Sent		Total Charged CDN	Proposed Billing Send Date	Cycle	Status	Actual Send Date	Rec'd Date
17	D14JAN20SAC	25-Line Exam-R	$1.25	143		15/01/2020	22/01/2020	Completed	Reporter	22/01/2020		$178.75	31/01/2020	Jan End	Completed	########	#########
18	D14FEB20SAC (McKerricher)	25-Line Exam-E	$1.40	90		14/02/2020	17/02/2020	Completed	Reporter	17/02/2020		$126.00	29/02/2020	Feb End	Completed	########	#########
19	D19fFEB20SA (G. Abra)	25-Line Exam-R	$1.25	96		29/02/2020			Reporter	06/03/2020		$120.00	29/02/2020	Feb End	Completed	########	#########
20	D19FEB20SA (T. Abra)	25-Line Exam-R	$1.25	22		29/02/2020			Reporter	06/03/2020		$27.50	15/04/2020	Mid Apr	Completed	########	#########
21	D08NOV17SA (Skittrell)	25-Line Exam-R	$1.25	32		29/02/2020			Reporter	05/03/2020		$40.00	30/06/2020	June End	Completed	########	#########
22	D23AUG19SA (Young)	25-Line Exam-R	$1.25	80		12/03/2020	17/03/2020	Completed	Reporter	16/03/2020		$100.00	15/09/2020	#REF!	Pending	########	#########
23	D18JUN19SA (Harper)	25-Line Exam-R	$1.25	106		12/03/2020	19/03/2020	Completed	Reporter	19/03/2020		$132.50	30/09/2020	#REF!	Pending	########	#########
24	D11FEB20SAC (LIM)	25-Line Exam-E	$1.40	80		06/04/2020	07/04/2020	Completed	Reporter	07/04/2020		$112.00	15/10/2020	#REF!	Pending
25	D27APR20SAC(Veldhoen)	25-Line Exam-R	$1.25	35		27/04/2020	04/05/2020	Completed	Reporter	01/05/2020		$43.75	31/10/2020	#REF!	Pending
26	D23APR20SA(OK)John Kuiper	25-Line Exam-R	$1.25	65		30/04/2020	04/05/2020	Completed	Reporter	04/05/2020		$81.25	15/11/2020	Oct End	Pending
27	D09APR18SA(OK) Blair	25-Line Exam-R	$1.25	26		06/05/2020	13/05/2020	Completed	Reporter	11/06/2020		$32.50	31/01/2020	Jan End	Completed
28	D09APR18SA(OK)R.Lohnes	25-Line Exam-R	$1.25	45		06/05/2020	13/05/2020	Completed	Reporter			$56.25
29	D09APR18SA(OK)Tran			9		06/05/2020	13/05/2020	Completed	Reporter
30	D02AUG19SA(OK)Wilkinson			45		13/05/2020	18/05/2020	Completed	Reporter
31	D15MAY20SACMcWhinnie	25-Line Exam-R	$1.25	84		15/05/2020	25/05/2020	Completed	Reporter	08/06/2020		$105.00	15/03/2020	June End	Completed
32	D28MAY20SAC (JESSA)	25-Line Exam-R	$1.25									$0.00
33
34
35	Leanne Kowalyk
36	Assignment					Dates/Recipient						Billing
37	Title of Job	Type of Proceeding & Service Level	Rate	Pages		Rec'd	Due	Status	Recipient	Sent		Total Charged CDN	Proposed Billing Send Date	Cycle	Status	Actual Send Date	Rec'd Date	Amount Rec'd USD
38	D21JAN20LK (Soroczka)	25-Line Exam-R	$1.25	69		22/01/2020	29/01/2020	Completed	Lindsay	28/01/2020		$86.25	31/01/2020	Jan End	Completed	########
39	D21JAN20LK (Wong)	25-Line Exam-R	$1.25	21		22/01/2020	29/01/2020	Completed	Lindsay	28/01/2020		$26.25	29/02/2020	Feb End	Completed	########
40	d19FEB20LK (smith	25-Line Exam-R	$1.25	75		24/02/2020	28/02/2020	Completed		28/02/2020		$93.75	29/02/2020	Feb End	Completed	########
41	D19FEB20LK (Kliewer)	25-Line Exam-R	$1.25	28		24/02/2020	28/02/2020	Completed		28/02/2020		$35.00	15/04/2020	Mid Apr	Completed	########
42	D24FEB20LK (Bennett)	25-Line Exam-R	$1.25	118		10/03/2020	12/03/2020	Completed	Erica	12/03/2020		$147.50	30/06/2020	June End	Completed	########
43	D29AUG19LK (Dhaliwal)	25-Line Exam-R	$1.25	55		11/03/2020	14/03/2020	Completed	Melissa	13/03/2020		$68.75	#VALUE!	#VALUE!	#VALUE!	########
44	D09DEC16LK (Islam)	25-Line Exam-R	$1.25	24		30/03/2020	03/04/2020	Completed	Jenni	02/04/2020		$30.00	#VALUE!	#VALUE!	#VALUE!
45
46
47	Keirstin Felten
48	Assignment					Dates/Recipient						Billing
49	Title of Job	Type of Proceeding & Service Level	Rate	Pages		Rec'd	Due	Status	Recipient	Sent		Total Charged CDN	Proposed Billing Send Date	Cycle	Status	Actual Send Date	Rec'd Date	Amount Rec'd USD
50	D31AUG16KF (Kourtessis)	25-Line Exam-R	$1.25	106		02/06/2020	08/06/2020	Completed	Erica			$132.50
51
52
53	Chris Linneman
54	Assignment					Dates/Recipient						Billing
55	Title of Job	Type of Proceeding & Service Level	Rate	Pages		Rec'd	Due	Status	Recipient	Sent		Total Charged CDN	Proposed Billing Send Date	Cycle	Status	Actual Send Date	Rec'd Date	Amount Rec'd USD
56	14JAN20CL SUN	25-Line Exam-R	$1.25	39		14/01/2020	21/02/2020	Completed	Reporter	17/01/2020		$48.75	31/01/2020	Jan End	Completed	########
57	D05FEB20CL (BATTH)	25-Line Exam-R	$1.25	60		05/02/2020	16/02/2020	Completed	Reporter	10/02/2020		$75.00	29/02/2020	Feb End	Completed	########
58	U19FEB20CL (White)	25-Line Exam-R	$1.25	55		20/02/2020	26/02/2020	Completed	Reporter	24/02/2020		$68.75	29/02/2020	Feb End	Completed	########
59	D06MAR20CL (A. Elliott)	25-Line Exam-R	$1.25	20		25/04/2020	5/4/20/20		Reporter			$25.00
60	D06MAR20CL (K. Elliott)	25-Line Exam-R	$1.25	128		25/04/2020	04/05/2020		Reporter	10/06/2020		$160.00	30/06/2020	June End	Completed
61
62
63	Christine Berekoff
64	Assignment					Dates/Recipient						Billing
65	Title of Job	Type of Proceeding & Service Level	Rate	Pages		Rec'd	Due	Status	Recipient	Sent		Total Charged CDN	Proposed Billing Send Date	Cycle	Status	Actual Send Date	Rec'd Date	Amount Rec'd USD
66	D27FEB20CB	25-Line Exam-R	$1.25	164		29/02/2020	08/03/2020	Completed	Reporter	08/03/2020		$205.00	31/01/2020	Jan End	Completed	########
67
68
69	Christy Pratt
70	Assignment					Dates/Recipient						Billing
71	Title of Job	Type of Proceeding & Service Level	Rate	Pages		Rec'd	Due	Status	Recipient	Sent		Total Charged CDN	Proposed Billing Send Date	Cycle	Status	Actual Send Date	Rec'd Date	Amount Rec'd USD
72	D14NOV19CP(Ahmadibiazi)	25-Line Exam-E	$1.40	129		29/04/2020	31/05/2020	Completed	Erica	31/05/2020		$180.60	31/01/2020	Jan End	Completed
Summary

Cell Formulas
Range	Formula
L7:L11,L72,L66,L56:L60,L50,L38:L44,L17:L32	L7	=IF((ISERROR(C7*D7))," ",(C7*D7))
M7,M72,M66,M56,M50,M38,M17	M7	=IF(J7="","",INDEX(Tables!$K$6:$K$31,SUMPRODUCT(($J$7>=Tables!$L$6:$L$31)*($J$7<=Tables!$M$6:$M$31)*MATCH(ROW(Tables!$K$6:$K$31),ROW(Tables!$K$6:$K$31)))))
N7,N72,N66,N56,N50,N38,N17	N7	=IF(M7="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$7>=Tables!$L$6:$L$32)*($M$7<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
O7:O11,O72,O66,O56:O60,O50,O38:O44,O17:O32	O7	=IF(M7="","",VLOOKUP(TODAY()-M7,Tables!$E$3:$F$17,2,1))
M8,M57,M39,M18	M8	=IF(J8="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$8>=Tables!$L$6:$L$30)*($J$8<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N8,N57,N39,N18	N8	=IF(M8="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$8>=Tables!$L$6:$L$32)*($M$8<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M9,M58,M40,M19	M9	=IF(J9="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$9>=Tables!$L$6:$L$30)*($J$9<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N9,N58,N40,N19	N9	=IF(M9="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$9>=Tables!$L$6:$L$32)*($M$9<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M10,M59,M41,M20	M10	=IF(J10="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$10>=Tables!$L$6:$L$30)*($J$10<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N10,N59,N41,N20	N10	=IF(M10="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$10>=Tables!$L$6:$L$32)*($M$10<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M11,M60,M42,M21	M11	=IF(J11="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$11>=Tables!$L$6:$L$30)*($J$11<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N11,N60,N42,N21	N11	=IF(M11="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$11>=Tables!$L$6:$L$32)*($M$11<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
C7:C11,C72,C66,C56:C60,C50,C38:C44,C17:C32	C7	=IF(B7="","",VLOOKUP(B7,Tables!$A$3:$B$8,2,FALSE))
H7:H11,H72,H66,H56:H58,H50,H38:H44,H17:H32	H7	=IF(G7="","",VLOOKUP(TODAY()-G7,Tables!$A$12:$C$26,3,1))
M22,M43	M22	=IF(J22="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$12>=Tables!$L$6:$L$30)*($J$12<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N22,N43	N22	=IF(M22="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$12>=Tables!$L$6:$L$32)*($M$12<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M23,M44	M23	=IF(J23="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$13>=Tables!$L$6:$L$30)*($J$13<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N23,N44	N23	=IF(M23="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$13>=Tables!$L$6:$L$32)*($M$13<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M24	M24	=IF(J24="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$14>=Tables!$L$6:$L$30)*($J$14<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N24	N24	=IF(M24="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$14>=Tables!$L$6:$L$32)*($M$14<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M25	M25	=IF(J25="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$15>=Tables!$L$6:$L$30)*($J$15<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N25	N25	=IF(M25="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$15>=Tables!$L$6:$L$32)*($M$15<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M26	M26	=IF(J26="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$16>=Tables!$L$6:$L$30)*($J$16<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N26	N26	=IF(M26="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$16>=Tables!$L$6:$L$32)*($M$16<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M27	M27	=IF(J27="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$17>=Tables!$L$6:$L$30)*($J$17<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N27	N27	=IF(M27="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$17>=Tables!$L$6:$L$32)*($M$17<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M28	M28	=IF(J28="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$18>=Tables!$L$6:$L$30)*($J$18<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N28	N28	=IF(M28="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$18>=Tables!$L$6:$L$32)*($M$18<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M29	M29	=IF(J29="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$19>=Tables!$L$6:$L$30)*($J$19<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N29,N32	N29	=IF(M29="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$19>=Tables!$L$6:$L$32)*($M$19<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M30	M30	=IF(J30="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$20>=Tables!$L$6:$L$30)*($J$20<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N30	N30	=IF(M30="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$20>=Tables!$L$6:$L$32)*($M$20<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M31	M31	=IF(J31="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$21>=Tables!$L$6:$L$30)*($J$21<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))
N31	N31	=IF(M31="","",INDEX(Tables!$J$6:$J$32,SUMPRODUCT(($M$21>=Tables!$L$6:$L$32)*($M$21<=Tables!$M$6:$M$32)*MATCH(ROW(Tables!$K$6:$K$32),ROW(Tables!$K$6:$K$32)))))
M32	M32	=IF(J32="","",INDEX(Tables!$K$6:$K$30,SUMPRODUCT(($J$22>=Tables!$L$6:$L$30)*($J$22<=Tables!$M$6:$M$30)*MATCH(ROW(Tables!$K$6:$K$30),ROW(Tables!$K$6:$K$30)))))

Named Ranges
Name	Refers To	Cells
'Chris Linneman'!Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
'Christine Berekoff'!Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
'Christy Pratt'!Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
'Keirstin Felten'!Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
'Laura Collis'!Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
'Leanne Kowalyk'!Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
'Sorrell-Ann Cadsky'!Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
Billing	=Date_Scheduler3436	N56:N60, N38:N44, N17:N32, N7:N11, N50, N66, N72
type	=Tables!$A$3:$B$8	C56:C60, C38:C44, C17:C32, C7:C11, C50, C66, C72

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O72	Cell Value	contains "1"	text	NO
O72	Cell Value	contains "2"	text	NO
O72	Cell Value	contains "3"	text	NO
O72	Cell Value	contains "Today"	text	NO
O72	Cell Value	contains "Pending"	text	NO
O72	Cell Value	contains "completed"	text	NO
N72	Cell Value	contains "Jan End"	text	NO
N72	Cell Value	contains "Dec End"	text	NO
N72	Cell Value	contains "Nov End"	text	NO
N72	Cell Value	contains "Oct End"	text	NO
N72	Cell Value	contains "Sept End"	text	NO
N72	Cell Value	contains "Aug End"	text	NO
N72	Cell Value	contains "July End"	text	NO
N72	Cell Value	contains "May End"	text	NO
N72	Cell Value	contains "Mar End"	text	NO
N72	Cell Value	contains "June End"	text	NO
N72	Cell Value	contains "Apr End"	text	NO
N72	Cell Value	contains "Feb End"	text	NO
N72	Cell Value	contains "Jan End,"	text	NO
H72	Cell Value	contains "3"	text	NO
H72	Cell Value	contains "2"	text	NO
H72	Cell Value	contains "1"	text	NO
H72	Cell Value	contains "Good"	text	NO
H72	Cell Value	contains "Completed"	text	NO
H72	Cell Value	contains "Today"	text	NO
O66	Cell Value	contains "1"	text	NO
O66	Cell Value	contains "2"	text	NO
O66	Cell Value	contains "3"	text	NO
O66	Cell Value	contains "Today"	text	NO
O66	Cell Value	contains "Pending"	text	NO
O66	Cell Value	contains "completed"	text	NO
N66	Cell Value	contains "Jan End"	text	NO
N66	Cell Value	contains "Dec End"	text	NO
N66	Cell Value	contains "Nov End"	text	NO
N66	Cell Value	contains "Oct End"	text	NO
N66	Cell Value	contains "Sept End"	text	NO
N66	Cell Value	contains "Aug End"	text	NO
N66	Cell Value	contains "July End"	text	NO
N66	Cell Value	contains "May End"	text	NO
N66	Cell Value	contains "Mar End"	text	NO
N66	Cell Value	contains "June End"	text	NO
N66	Cell Value	contains "Apr End"	text	NO
N66	Cell Value	contains "Feb End"	text	NO
N66	Cell Value	contains "Jan End,"	text	NO
H66	Cell Value	contains "3"	text	NO
H66	Cell Value	contains "2"	text	NO
H66	Cell Value	contains "1"	text	NO
H66	Cell Value	contains "Good"	text	NO
H66	Cell Value	contains "Completed"	text	NO
H66	Cell Value	contains "Today"	text	NO
O56:O60	Cell Value	contains "1"	text	NO
O56:O60	Cell Value	contains "2"	text	NO
O56:O60	Cell Value	contains "3"	text	NO
O56:O60	Cell Value	contains "Today"	text	NO
O56:O60	Cell Value	contains "Pending"	text	NO
O56:O60	Cell Value	contains "completed"	text	NO
N56:N60	Cell Value	contains "Jan End"	text	NO
N56:N60	Cell Value	contains "Dec End"	text	NO
N56:N60	Cell Value	contains "Nov End"	text	NO
N56:N60	Cell Value	contains "Oct End"	text	NO
N56:N60	Cell Value	contains "Sept End"	text	NO
N56:N60	Cell Value	contains "Aug End"	text	NO
N56:N60	Cell Value	contains "July End"	text	NO
N56:N60	Cell Value	contains "May End"	text	NO
N56:N60	Cell Value	contains "Mar End"	text	NO
N56:N60	Cell Value	contains "June End"	text	NO
N56:N60	Cell Value	contains "Apr End"	text	NO
N56:N60	Cell Value	contains "Feb End"	text	NO
N56:N60	Cell Value	contains "Jan End,"	text	NO
H56:H60	Cell Value	contains "3"	text	NO
H56:H60	Cell Value	contains "2"	text	NO
H56:H60	Cell Value	contains "1"	text	NO
H56:H60	Cell Value	contains "Good"	text	NO
H56:H60	Cell Value	contains "Completed"	text	NO
H56:H60	Cell Value	contains "Today"	text	NO
O50	Cell Value	contains "1"	text	NO
O50	Cell Value	contains "2"	text	NO
O50	Cell Value	contains "3"	text	NO
O50	Cell Value	contains "Today"	text	NO
O50	Cell Value	contains "Pending"	text	NO
O50	Cell Value	contains "completed"	text	NO
N50	Cell Value	contains "Jan End"	text	NO
N50	Cell Value	contains "Dec End"	text	NO
N50	Cell Value	contains "Nov End"	text	NO
N50	Cell Value	contains "Oct End"	text	NO
N50	Cell Value	contains "Sept End"	text	NO
N50	Cell Value	contains "Aug End"	text	NO
N50	Cell Value	contains "July End"	text	NO
N50	Cell Value	contains "May End"	text	NO
N50	Cell Value	contains "Mar End"	text	NO
N50	Cell Value	contains "June End"	text	NO
N50	Cell Value	contains "Apr End"	text	NO
N50	Cell Value	contains "Feb End"	text	NO
N50	Cell Value	contains "Jan End,"	text	NO
H50	Cell Value	contains "3"	text	NO
H50	Cell Value	contains "2"	text	NO
H50	Cell Value	contains "1"	text	NO
H50	Cell Value	contains "Good"	text	NO
H50	Cell Value	contains "Completed"	text	NO
H50	Cell Value	contains "Today"	text	NO
O38:O44	Cell Value	contains "1"	text	NO
O38:O44	Cell Value	contains "2"	text	NO
O38:O44	Cell Value	contains "3"	text	NO
O38:O44	Cell Value	contains "Today"	text	NO
O38:O44	Cell Value	contains "Pending"	text	NO
O38:O44	Cell Value	contains "completed"	text	NO
N38:N44	Cell Value	contains "Jan End"	text	NO
N38:N44	Cell Value	contains "Dec End"	text	NO
N38:N44	Cell Value	contains "Nov End"	text	NO
N38:N44	Cell Value	contains "Oct End"	text	NO
N38:N44	Cell Value	contains "Sept End"	text	NO
N38:N44	Cell Value	contains "Aug End"	text	NO
N38:N44	Cell Value	contains "July End"	text	NO
N38:N44	Cell Value	contains "May End"	text	NO
N38:N44	Cell Value	contains "Mar End"	text	NO
N38:N44	Cell Value	contains "June End"	text	NO
N38:N44	Cell Value	contains "Apr End"	text	NO
N38:N44	Cell Value	contains "Feb End"	text	NO
N38:N44	Cell Value	contains "Jan End,"	text	NO
H38:H44	Cell Value	contains "3"	text	NO
H38:H44	Cell Value	contains "2"	text	NO
H38:H44	Cell Value	contains "1"	text	NO
H38:H44	Cell Value	contains "Good"	text	NO
H38:H44	Cell Value	contains "Completed"	text	NO
H38:H44	Cell Value	contains "Today"	text	NO
O17:O32	Cell Value	contains "1"	text	NO
O17:O32	Cell Value	contains "2"	text	NO
O17:O32	Cell Value	contains "3"	text	NO
O17:O32	Cell Value	contains "Today"	text	NO
O17:O32	Cell Value	contains "Pending"	text	NO
O17:O32	Cell Value	contains "completed"	text	NO
N17:N32	Cell Value	contains "Jan End"	text	NO
N17:N32	Cell Value	contains "Dec End"	text	NO
N17:N32	Cell Value	contains "Nov End"	text	NO
N17:N32	Cell Value	contains "Oct End"	text	NO
N17:N32	Cell Value	contains "Sept End"	text	NO
N17:N32	Cell Value	contains "Aug End"	text	NO
N17:N32	Cell Value	contains "July End"	text	NO
N17:N32	Cell Value	contains "May End"	text	NO
N17:N32	Cell Value	contains "Mar End"	text	NO
N17:N32	Cell Value	contains "June End"	text	NO
N17:N32	Cell Value	contains "Apr End"	text	NO
N17:N32	Cell Value	contains "Feb End"	text	NO
N17:N32	Cell Value	contains "Jan End,"	text	NO
H17:H32	Cell Value	contains "3"	text	NO
H17:H32	Cell Value	contains "2"	text	NO
H17:H32	Cell Value	contains "1"	text	NO
H17:H32	Cell Value	contains "Good"	text	NO
H17:H32	Cell Value	contains "Completed"	text	NO
H17:H32	Cell Value	contains "Today"	text	NO
O7:O11	Cell Value	contains "1"	text	NO
O7:O11	Cell Value	contains "2"	text	NO
O7:O11	Cell Value	contains "3"	text	NO
O7:O11	Cell Value	contains "Today"	text	NO
O7:O11	Cell Value	contains "Pending"	text	NO
O7:O11	Cell Value	contains "completed"	text	NO
N7:N11	Cell Value	contains "Jan End"	text	NO
N7:N11	Cell Value	contains "Dec End"	text	NO
N7:N11	Cell Value	contains "Nov End"	text	NO
N7:N11	Cell Value	contains "Oct End"	text	NO
N7:N11	Cell Value	contains "Sept End"	text	NO
N7:N11	Cell Value	contains "Aug End"	text	NO
N7:N11	Cell Value	contains "July End"	text	NO
N7:N11	Cell Value	contains "May End"	text	NO
N7:N11	Cell Value	contains "Mar End"	text	NO
N7:N11	Cell Value	contains "June End"	text	NO
N7:N11	Cell Value	contains "Apr End"	text	NO
N7:N11	Cell Value	contains "Feb End"	text	NO
N7:N11	Cell Value	contains "Jan End,"	text	NO
H7:H11	Cell Value	contains "3"	text	NO
H7:H11	Cell Value	contains "2"	text	NO
H7:H11	Cell Value	contains "1"	text	NO
H7:H11	Cell Value	contains "Good"	text	NO
H7:H11	Cell Value	contains "Completed"	text	NO
H7:H11	Cell Value	contains "Today"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I7:I9,I291:I293,I88:I89,I78:I80,I149:I151,I159:I160,I220:I222,I230:I231,I17:I18	List	=Tables!$H$1:$H$3
I56:I60	List	=Tables!$H$1:$H$4
B56:B60	List	=Tables!$A$2:$A$8
B38:B44	List	=Tables!$A$2:$A$8
B17:B28	List	=Tables!$A$2:$A$8
I38:I44	List	=Tables!$H$1:$H$8
B31:B32	List	=Tables!$A$3:$A$8

 

[jthomas3029]

I have a table in the sheet labeled Tables that generates the due date based on the start date/end date range. It is working on the worksheets but when it is transferred over to the Summary sheet some, but not all, of the dates are in error even though the formulas registered on the Summary sheet are correct.

 

[MARK858]

Can you give examples using the cell references we are looking at in the Summary sheet please.

 

[jthomas3029]

I am sorry. I was waiting for you and and you were waiting for me to respond.

Line 19 Column J and Column M are correct on the worksheet but not on the summary sheet even though the formulas on the summary sheet are correct

 

[MARK858]

What if you change $J$9 to $J9 in the formula on the Sorrell-Ann Cadsky sheet?

 

[jthomas3029]

By Jove! I think that has resolved the problem. Can I ask why?