Offset Function/Dynamic Charts - dates not aligning

[Dynawoman]

Hi - I am looking for some help with the offset function and dynamic charts. I have created my defined names and created the chart; however, the dates are not aligning properly. I cannot find any information on what data types can be used in these defined names. Some of the data are formulas. Is this causing the problem?

Scenario: each day the chart is updated with new data and we have to remove a day and add a day. I don't want to have to manually do this, I want it done automatically. I want the chart to always display the last 60 days. I'm using =OFFSET($A$5,0,COUNTA($B$5:$RU$5),1,-60). I originally had it using the entire row 5, but changed it to B-RU to see if that would help and no such luck. There is a formula in the Day-Date cell: =B4&" - " & TEXT(B2,"m/dd"). I also changed this to be a custom date and the data still does not align. Is there a different function I should be using? Any help is appreciated. TYIA!

 

[Dynawoman]

Test Dynamic Charts2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Long Date	12/1/2022	12/2/2022	12/3/2022	12/4/2022	12/5/2022	12/6/2022	12/7/2022	12/8/2022	12/9/2022	12/10/2022	12/11/2022	12/12/2022
2	Short Date:	12/01	12/02	12/03	12/04	12/05	12/06	12/07	12/08	12/09	12/10	12/11	12/12
3	Week of Year:	49	49	49	50	50	50	50	50	50	50	51	51
4	Day of Week:	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
5	Day - Date:	Thu - 12/01	Fri - 12/02	Sat - 12/03	Sun - 12/04	Mon - 12/05	Tue - 12/06	Wed - 12/07	Thu - 12/08	Fri - 12/09	Sat - 12/10	Sun - 12/11	Mon - 12/12
6	CC Review:	575	540	614	803	803	669	729	789	432	321	506	475
7	DE - ALF:	81	105	98	123	149	125	41	14	32	72	78	113
8	DE - BF:	108	114	133	141	154	178	130	93	104	116	131	151
9	DE - CHR:	236	200	250	270	309	271	234	203	208	258	279	311
10	DE - ER:	16	21	31	40	45	56	1	8	21	22	31	42
11	DE - IF:	887	852	835	781	840	719	716	665	634	595	526	593
12	DE - MP:	6	4	4	4	4	0	2	2	2	7	6	6
13	DE - NM:	1,425	1,409	1,450	1,447	1,559	1,509	1,464	1,401	1,434	1,463	1,488	1,606
14	DE - SM:	428	439	450	488	530	516	392	361	364	356	407	463
15	DE - ST:	513	499	497	523	563	581	589	554	565	573	595	620
16	DB:	129	52	59	59	59	92	101	118	78	95	95	96
17	DE Total w/C & D:	4,404	4,235	4,421	4,679	5,015	4,716	4,399	4,208	3,874	3,878	4,142	4,476
18	DE w/C:	4,275	4,183	4,362	4,620	4,956	4,624	4,298	4,090	3,796	3,783	4,047	4,380
19	DE Cnt w/D:	3,829	3,695	3,807	3,876	4,212	4,047	3,670	3,419	3,442	3,557	3,636	4,001
20	DE WQ Only Cnt:	3,700	3,643	3,748	3,817	4,153	3,955	3,569	3,301	3,364	3,462	3,541	3,905
21	Daily Change in DE WQ Only Cnt:	(93)	(57)	105	69	336	(198)	(386)	(268)	63	98	79	364
22	Weekly Change in DE Only Cnt:	828	463	448	247	257	51	(224)	(399)	(279)	(286)	(276)	(248)
23	Weekly Change in DE + DG Cnt:	849	407	399	198	208	31	(245)	(410)	(253)	(250)	(240)	(211)
24	Running Av DE Only - 30 days:	2,832	2,876	2,929	2,986	3,049	3,096	3,121	3,142	3,174	3,215	3,260	3,310
25	Running Av DE Only - 90 days:	2,627	2,645	2,658	2,670	2,685	2,697	2,708	2,718	2,727	2,734	2,739	2,748
26	WQ Total Cnt Goal:	2,200	2,200	2,200	2,200	2,200	2,200	2,200	2,200	2,200	2,200	2,200	2,200
Daily Charts

Cell Formulas
Range	Formula
B3:M3	B3	=WEEKNUM(B1)
B4	B4	=VLOOKUP(WEEKDAY(B1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
C4	C4	=VLOOKUP(WEEKDAY(C1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
D4	D4	=VLOOKUP(WEEKDAY(D1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
E4	E4	=VLOOKUP(WEEKDAY(E1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
F4	F4	=VLOOKUP(WEEKDAY(F1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
G4	G4	=VLOOKUP(WEEKDAY(G1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
H4	H4	=VLOOKUP(WEEKDAY(H1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
I4	I4	=VLOOKUP(WEEKDAY(I1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
J4	J4	=VLOOKUP(WEEKDAY(J1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
K4	K4	=VLOOKUP(WEEKDAY(K1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
L4	L4	=VLOOKUP(WEEKDAY(L1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
M4	M4	=VLOOKUP(WEEKDAY(M1), 'Day of Week X Walk'!$A$2:D8, 3, FALSE)
B5:M5	B5	=B4&" - "& TEXT(B2, "m/dd")
B17:M17	B17	=SUM(B6:B16)
B18:M19	B18	=SUM(B6:B15)
B20:M20	B20	=SUM(B7:B15)

 

[ExceLoki]

can you add another sheet to your workbook for your chart to actually pull from? this sheet can pull the last 60 days from the main data sheet.
you can keep adding to your main data sheet, but the chart data sheet would only see the last 60 days.

Book1
	A	B	C	D	E	F																																																		BD	BE	BF	BG	BH	BI
1	Long Date	1/29/2023	1/30/2023	1/31/2023	2/1/2023	2/2/2023																																																		3/24/2023	3/25/2023	3/26/2023	3/27/2023	3/28/2023	3/29/2023
2	Short Date:	1/29	1/30	1/31	2/1	2/2																																																		3/24	3/25	3/26	3/27	3/28	3/29
3	Week of Year:	5	5	5	5	5																																																		12	12	13	13	13	13
4	Day of Week:	Sun	Mon	Tue	Wed	Thu																																																		Fri	Sat	Sun	Mon	Tue	Wed
5	Day - Date:	Sun - 01/29	Mon - 01/30	Tue - 01/31	Wed - 02/01	Thu - 02/02																																																		Fri - 03/24	Sat - 03/25	Sun - 03/26	Mon - 03/27	Tue - 03/28	Wed - 03/29
6	CC Review:	-386	-404	-423	-441	-460																																																		-1385	-1404	-1422	-1441	-1459	-1478
7	DE - ALF:	-104	-108	-111	-115	-119																																																		-296	-300	-303	-307	-311	-314
8	DE - BF:	146	147	147	147	148																																																		164	164	164	165	165	165
9	DE - CHR:	426	429	432	436	439																																																		601	604	607	611	614	617
10	DE - ER:	38	38	38	38	38																																																		47	48	48	48	48	48
11	DE - IF:	-984	-1016	-1048	-1080	-1112																																																		-2704	-2736	-2768	-2800	-2832	-2864
12	DE - MP:	9	9	9	9	9																																																		13	13	13	14	14	14
13	DE - NM:	1884	1892	1900	1908	1915																																																		2302	2309	2317	2325	2332	2340
14	DE - SM:	94	88	81	75	69																																																		-248	-255	-261	-267	-274	-280
15	DE - ST:	1073	1083	1092	1102	1112																																																		1595	1605	1614	1624	1634	1643
16	DB:	190	192	194	196	198																																																		295	297	299	301	303	305
17	DE Total w/C & D:	2386	2349	2312	2275	2238																																																		383	346	309	272	235	197
18	DE w/C:	2196	2157	2118	2079	2040																																																		87	48	9	-30	-69	-108
19	DE Cnt w/D:	2772	2753	2735	2716	2697																																																		1768	1750	1731	1712	1694	1675
20	DE WQ Only Cnt:	2581	2561	2540	2520	2499																																																		1473	1452	1432	1411	1391	1370
21	Daily Change in DE WQ Only Cnt:	790	804	819	834	848																																																		1577	1592	1607	1621	1636	1650
22	Weekly Change in DE Only Cnt:	-5282	-5382	-5482	-5581	-5681																																																		-10663	-10763	-10862	-10962	-11062	-11161
23	Weekly Change in DE + DG Cnt:	-4900	-4993	-5085	-5177	-5270																																																		-9887	-9979	-10072	-10164	-10256	-10349
24	Running Av DE Only - 30 days:	5317.74468	5359.52615	5401.30761	5443.08908	5484.87055																																																		7573.943978	7615.725447	7657.506915	7699.288384	7741.069852	7782.851321
25	Running Av DE Only - 90 days:	3276.12131	3286.95712	3297.79292	3308.62873	3319.46453																																																		3861.254831	3872.090636	3882.926442	3893.762248	3904.598054	3915.43386
26	WQ Total Cnt Goal:	2200	2200	2200	2200	2200																																																		2200	2200	2200	2200	2200	2200
chart data

Cell Formulas
Range	Formula
B1:F1,BD1:BH1	B1	=C1-1
BI1	BI1	=TODAY()
B2:F2,BD2:BI2	B2	=B1
B3:F3,BD3:BI3	B3	=WEEKNUM(B1)
B4:F4,BD4:BI4	B4	=B1
B5:F5,BD5:BI5	B5	=B1
A6:BI26	A6	=FILTER(data!6:26,data!1:1>='chart data'!B1,"")
Dynamic array formulas.

*i had to hide many columns in order to be able to use plugin, but this should give you an idea

 

[Dynawoman]

can you add another sheet to your workbook for your chart to actually pull from? this sheet can pull the last 60 days from the main data sheet.
you can keep adding to your main data sheet, but the chart data sheet would only see the last 60 days.

Book1
	A	B	C	D	E	F																																																		BD	BE	BF	BG	BH	BI
1	Long Date	1/29/2023	1/30/2023	1/31/2023	2/1/2023	2/2/2023																																																		3/24/2023	3/25/2023	3/26/2023	3/27/2023	3/28/2023	3/29/2023
2	Short Date:	1/29	1/30	1/31	2/1	2/2																																																		3/24	3/25	3/26	3/27	3/28	3/29
3	Week of Year:	5	5	5	5	5																																																		12	12	13	13	13	13
4	Day of Week:	Sun	Mon	Tue	Wed	Thu																																																		Fri	Sat	Sun	Mon	Tue	Wed
5	Day - Date:	Sun - 01/29	Mon - 01/30	Tue - 01/31	Wed - 02/01	Thu - 02/02																																																		Fri - 03/24	Sat - 03/25	Sun - 03/26	Mon - 03/27	Tue - 03/28	Wed - 03/29
6	CC Review:	-386	-404	-423	-441	-460																																																		-1385	-1404	-1422	-1441	-1459	-1478
7	DE - ALF:	-104	-108	-111	-115	-119																																																		-296	-300	-303	-307	-311	-314
8	DE - BF:	146	147	147	147	148																																																		164	164	164	165	165	165
9	DE - CHR:	426	429	432	436	439																																																		601	604	607	611	614	617
10	DE - ER:	38	38	38	38	38																																																		47	48	48	48	48	48
11	DE - IF:	-984	-1016	-1048	-1080	-1112																																																		-2704	-2736	-2768	-2800	-2832	-2864
12	DE - MP:	9	9	9	9	9																																																		13	13	13	14	14	14
13	DE - NM:	1884	1892	1900	1908	1915																																																		2302	2309	2317	2325	2332	2340
14	DE - SM:	94	88	81	75	69																																																		-248	-255	-261	-267	-274	-280
15	DE - ST:	1073	1083	1092	1102	1112																																																		1595	1605	1614	1624	1634	1643
16	DB:	190	192	194	196	198																																																		295	297	299	301	303	305
17	DE Total w/C & D:	2386	2349	2312	2275	2238																																																		383	346	309	272	235	197
18	DE w/C:	2196	2157	2118	2079	2040																																																		87	48	9	-30	-69	-108
19	DE Cnt w/D:	2772	2753	2735	2716	2697																																																		1768	1750	1731	1712	1694	1675
20	DE WQ Only Cnt:	2581	2561	2540	2520	2499																																																		1473	1452	1432	1411	1391	1370
21	Daily Change in DE WQ Only Cnt:	790	804	819	834	848																																																		1577	1592	1607	1621	1636	1650
22	Weekly Change in DE Only Cnt:	-5282	-5382	-5482	-5581	-5681																																																		-10663	-10763	-10862	-10962	-11062	-11161
23	Weekly Change in DE + DG Cnt:	-4900	-4993	-5085	-5177	-5270																																																		-9887	-9979	-10072	-10164	-10256	-10349
24	Running Av DE Only - 30 days:	5317.74468	5359.52615	5401.30761	5443.08908	5484.87055																																																		7573.943978	7615.725447	7657.506915	7699.288384	7741.069852	7782.851321
25	Running Av DE Only - 90 days:	3276.12131	3286.95712	3297.79292	3308.62873	3319.46453																																																		3861.254831	3872.090636	3882.926442	3893.762248	3904.598054	3915.43386
26	WQ Total Cnt Goal:	2200	2200	2200	2200	2200																																																		2200	2200	2200	2200	2200	2200
chart data

Cell Formulas
Range	Formula
B1:F1,BD1:BH1	B1	=C1-1
BI1	BI1	=TODAY()
B2:F2,BD2:BI2	B2	=B1
B3:F3,BD3:BI3	B3	=WEEKNUM(B1)
B4:F4,BD4:BI4	B4	=B1
B5:F5,BD5:BI5	B5	=B1
A6:BI26	A6	=FILTER(data!6:26,data!1:1>='chart data'!B1,"")
Dynamic array formulas.

*i had to hide many columns in order to be able to use plugin, but this should give you an idea

I could do that. What function would you suggest using? XLOOKUP? I was wondering if my problem was using calculated data vs raw data in my spreadsheet? Does anyone know if the Offset function has issues with calculated data vs raw data? You wouldn't think it should matter, but I am wondering if it does.

 

[ExceLoki]

in the above post i have it filtering from the main data sheet based on date, and it returns all the values from -60 thru today()
just paste in the formula. (probably will need to update the sheet name for the running data sheet in the formula)
this will giving you the running 60 days

=FILTER(data!6:26,data!1:1>=B1,"")

to c6 under this:

Offset Function-Dynamic Charts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	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
1	Long Date	1/29/2023	1/30/2023	1/31/2023	2/1/2023	2/2/2023	2/3/2023	2/4/2023	2/5/2023	2/6/2023	2/7/2023	2/8/2023	2/9/2023	2/10/2023	2/11/2023	2/12/2023	2/13/2023	2/14/2023	2/15/2023	2/16/2023	2/17/2023	2/18/2023	2/19/2023	2/20/2023	2/21/2023	2/22/2023	2/23/2023	2/24/2023	2/25/2023	2/26/2023	2/27/2023	2/28/2023	3/1/2023	3/2/2023	3/3/2023	3/4/2023	3/5/2023	3/6/2023	3/7/2023	3/8/2023	3/9/2023	3/10/2023	3/11/2023	3/12/2023	3/13/2023	3/14/2023	3/15/2023	3/16/2023	3/17/2023	3/18/2023	3/19/2023	3/20/2023	3/21/2023	3/22/2023	3/23/2023	3/24/2023	3/25/2023	3/26/2023	3/27/2023	3/28/2023	3/29/2023
2	Short Date:	1/29	1/30	1/31	2/1	2/2	2/3	2/4	2/5	2/6	2/7	2/8	2/9	2/10	2/11	2/12	2/13	2/14	2/15	2/16	2/17	2/18	2/19	2/20	2/21	2/22	2/23	2/24	2/25	2/26	2/27	2/28	3/1	3/2	3/3	3/4	3/5	3/6	3/7	3/8	3/9	3/10	3/11	3/12	3/13	3/14	3/15	3/16	3/17	3/18	3/19	3/20	3/21	3/22	3/23	3/24	3/25	3/26	3/27	3/28	3/29
3	Week of Year:	5	5	5	5	5	5	5	6	6	6	6	6	6	6	7	7	7	7	7	7	7	8	8	8	8	8	8	8	9	9	9	9	9	9	9	10	10	10	10	10	10	10	11	11	11	11	11	11	11	12	12	12	12	12	12	12	13	13	13	13
4	Day of Week:	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
5	Day - Date:	Sun - 01/29	Mon - 01/30	Tue - 01/31	Wed - 02/01	Thu - 02/02	Fri - 02/03	Sat - 02/04	Sun - 02/05	Mon - 02/06	Tue - 02/07	Wed - 02/08	Thu - 02/09	Fri - 02/10	Sat - 02/11	Sun - 02/12	Mon - 02/13	Tue - 02/14	Wed - 02/15	Thu - 02/16	Fri - 02/17	Sat - 02/18	Sun - 02/19	Mon - 02/20	Tue - 02/21	Wed - 02/22	Thu - 02/23	Fri - 02/24	Sat - 02/25	Sun - 02/26	Mon - 02/27	Tue - 02/28	Wed - 03/01	Thu - 03/02	Fri - 03/03	Sat - 03/04	Sun - 03/05	Mon - 03/06	Tue - 03/07	Wed - 03/08	Thu - 03/09	Fri - 03/10	Sat - 03/11	Sun - 03/12	Mon - 03/13	Tue - 03/14	Wed - 03/15	Thu - 03/16	Fri - 03/17	Sat - 03/18	Sun - 03/19	Mon - 03/20	Tue - 03/21	Wed - 03/22	Thu - 03/23	Fri - 03/24	Sat - 03/25	Sun - 03/26	Mon - 03/27	Tue - 03/28	Wed - 03/29
chart data

Cell Formulas
Range	Formula
B1:BH1	B1	=C1-1
BI1	BI1	=TODAY()
B2:BI2	B2	=B1
B3:BI3	B3	=WEEKNUM(B1)
B4:BI4	B4	=B1
B5:BI5	B5	=B1

 

[Dynawoman]

in the above post i have it filtering from the main data sheet based on date, and it returns all the values from -60 thru today()
just paste in the formula. (probably will need to update the sheet name for the running data sheet in the formula)
this will giving you the running 60 days

=FILTER(data!6:26,data!1:1>=B1,"")

to c6 under this:

Offset Function-Dynamic Charts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	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
1	Long Date	1/29/2023	1/30/2023	1/31/2023	2/1/2023	2/2/2023	2/3/2023	2/4/2023	2/5/2023	2/6/2023	2/7/2023	2/8/2023	2/9/2023	2/10/2023	2/11/2023	2/12/2023	2/13/2023	2/14/2023	2/15/2023	2/16/2023	2/17/2023	2/18/2023	2/19/2023	2/20/2023	2/21/2023	2/22/2023	2/23/2023	2/24/2023	2/25/2023	2/26/2023	2/27/2023	2/28/2023	3/1/2023	3/2/2023	3/3/2023	3/4/2023	3/5/2023	3/6/2023	3/7/2023	3/8/2023	3/9/2023	3/10/2023	3/11/2023	3/12/2023	3/13/2023	3/14/2023	3/15/2023	3/16/2023	3/17/2023	3/18/2023	3/19/2023	3/20/2023	3/21/2023	3/22/2023	3/23/2023	3/24/2023	3/25/2023	3/26/2023	3/27/2023	3/28/2023	3/29/2023
2	Short Date:	1/29	1/30	1/31	2/1	2/2	2/3	2/4	2/5	2/6	2/7	2/8	2/9	2/10	2/11	2/12	2/13	2/14	2/15	2/16	2/17	2/18	2/19	2/20	2/21	2/22	2/23	2/24	2/25	2/26	2/27	2/28	3/1	3/2	3/3	3/4	3/5	3/6	3/7	3/8	3/9	3/10	3/11	3/12	3/13	3/14	3/15	3/16	3/17	3/18	3/19	3/20	3/21	3/22	3/23	3/24	3/25	3/26	3/27	3/28	3/29
3	Week of Year:	5	5	5	5	5	5	5	6	6	6	6	6	6	6	7	7	7	7	7	7	7	8	8	8	8	8	8	8	9	9	9	9	9	9	9	10	10	10	10	10	10	10	11	11	11	11	11	11	11	12	12	12	12	12	12	12	13	13	13	13
4	Day of Week:	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
5	Day - Date:	Sun - 01/29	Mon - 01/30	Tue - 01/31	Wed - 02/01	Thu - 02/02	Fri - 02/03	Sat - 02/04	Sun - 02/05	Mon - 02/06	Tue - 02/07	Wed - 02/08	Thu - 02/09	Fri - 02/10	Sat - 02/11	Sun - 02/12	Mon - 02/13	Tue - 02/14	Wed - 02/15	Thu - 02/16	Fri - 02/17	Sat - 02/18	Sun - 02/19	Mon - 02/20	Tue - 02/21	Wed - 02/22	Thu - 02/23	Fri - 02/24	Sat - 02/25	Sun - 02/26	Mon - 02/27	Tue - 02/28	Wed - 03/01	Thu - 03/02	Fri - 03/03	Sat - 03/04	Sun - 03/05	Mon - 03/06	Tue - 03/07	Wed - 03/08	Thu - 03/09	Fri - 03/10	Sat - 03/11	Sun - 03/12	Mon - 03/13	Tue - 03/14	Wed - 03/15	Thu - 03/16	Fri - 03/17	Sat - 03/18	Sun - 03/19	Mon - 03/20	Tue - 03/21	Wed - 03/22	Thu - 03/23	Fri - 03/24	Sat - 03/25	Sun - 03/26	Mon - 03/27	Tue - 03/28	Wed - 03/29
chart data

Cell Formulas
Range	Formula
B1:BH1	B1	=C1-1
BI1	BI1	=TODAY()
B2:BI2	B2	=B1
B3:BI3	B3	=WEEKNUM(B1)
B4:BI4	B4	=B1
B5:BI5	B5	=B1

Great - thank you! I think I figured out my issues. There were NA values in the data set and blanks. I didn't see those until going through column by column as the data set is so large. I am going to try it adding a formula to fix those cells and try again. Thank you again for your help. I will keep your formula handy as well if all fails.