Moving cells on to another worksheet

[Marvo]

HI,

I have the following on a worksheet

Cobblers.xlsm
	CQ	CR	CS	CT
5559	CURRENT FORM
5560	GAMES	12
5561	LEAGUE	13
5562	ALL COMPETITIONS	13
5563
5564	BETTER		POINTS	DATE
5565	LEAGUE	$B$5549	14	9/03/2024
5566	ALL COMPETITIONS	$B$5549	14	9/03/2024
5567
5568	WORSE
5569	LEAGUE	$B$5551	10	16/03/2024
5570	ALL COMPETITIONS	$B$5551	10	16/03/2024
ALL

Cell Formulas
Range	Formula
CR5561	CR5561	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),(Alltable[Competition]=$CQ$5561)*(Alltable[Result]<>"")),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-ALL!$CR$5560)))
CR5562	CR5562	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),Alltable[Result]<>""),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-ALL!$CR$5560)))
CR5565	CR5565	=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5558>$CR$5561,ROW($AG$1:$AG5558)))*1,2)
CS5565	CS5565	=INDIRECT("AG"&MID($CR$5565,FIND("$",$CR$5565,2),7))
CT5565	CT5565	=HYPERLINK("#ALL!"&$CR$5565,TEXT(INDIRECT($CR$5565),"d/mm/yyyy"))
CR5566	CR5566	=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5558>$CR$5562,ROW($AF$1:$AF5558)))*1,2)
CS5566	CS5566	=INDIRECT("AF"&MID($CR$5566,FIND("$",$CR$5566,2),7))
CT5566	CT5566	=HYPERLINK("#ALL!"&$CR$5566,TEXT(INDIRECT($CR$5566),"d/mm/yyyy"))
CR5569	CR5569	=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5551<$CR$5561,ROW($AG$1:$AG5551)))*1,2)
CS5569	CS5569	=INDIRECT("AG"&MID($CR$5569,FIND("$",$CR$5569,2),7))
CT5569	CT5569	=HYPERLINK("#ALL!"&$CR$5569,TEXT(INDIRECT($CR$5569),"d/mm/yyyy"))
CR5570	CR5570	=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5551<$CR$5562,ROW($AF$1:$AF5551)))*1,2)
CS5570	CS5570	=INDIRECT("AF"&MID($CR$5570,FIND("$",$CR$5570,2),7))
CT5570	CT5570	=HYPERLINK("#ALL!"&$CR$5570,TEXT(INDIRECT($CR$5570),"d/mm/yyyy"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S1149:S1173,R1:R5560,CQ5559:CQ5570	Cell Value	="Northampton Town"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="Play-offs"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="League Trophy"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="League Cup"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="Div 3 South Cup"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="FA Cup"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="Charity Shield"	text	NO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564	Cell Value	="FL 3rd Tier"	text	NO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564	Cell Value	="FL 4th Tier"	text	NO
CQ5559:CQ5570	Expression	=E5561="H"	text	NO

Which I would like to move to another sheet but I don't know what is the formula to change. When I move it, it stops working

Cobblers.xlsm
	A	B	C	D
1	CURRENT FORM
2	GAMES	12
3	LEAGUE	13
4	ALL COMPETITIONS	13
5
6	BETTER		POINTS	DATE
7	LEAGUE	$B$5549	0	0/01/1900
8	ALL COMPETITIONS	$B$5549	0	0/01/1900
9
10	WORSE
11	LEAGUE	$B$5558	0	0/01/1900
12	ALL COMPETITIONS	$B$5558	0	0/01/1900
Current Form

Cell Formulas
Range	Formula
B3	B3	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),(Alltable[Competition]="League")*(Alltable[Result]<>"")),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-B2)))
B4	B4	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),Alltable[Result]<>""),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-B2)))
B7	B7	=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5558>B3,ROW(ALL!$AG$1:$AG5558)))*1,2)
C7,C11	C7	=INDIRECT("AG"&MID(B7,FIND("$",B7,2),7))
D7:D8,D11:D12	D7	=HYPERLINK("#ALL!"&B7,TEXT(INDIRECT(B7),"d/mm/yyyy"))
B8	B8	=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5558>B4,ROW(ALL!$AF$1:$AF5558)))*1,2)
C8,C12	C8	=INDIRECT("AF"&MID(B8,FIND("$",B8,2),7))
B11	B11	=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5558<B3,ROW(ALL!$AG$1:$AG5558)))*1,2)
B12	B12	=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5558<B4,ROW(ALL!$AF$1:$AF5558)))*1,2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A12	Cell Value	="Northampton Town"	text	NO
D9:D10,D1:D6	Cell Value	="Play-offs"	text	NO
D9:D10,D1:D6	Cell Value	="League Trophy"	text	NO
D9:D10,D1:D6	Cell Value	="League Cup"	text	NO
D9:D10,D1:D6	Cell Value	="Div 3 South Cup"	text	NO
D9:D10,D1:D6	Cell Value	="FA Cup"	text	NO
D9:D10,D1:D6	Cell Value	="Charity Shield"	text	NO
C9:C10,C1:C6	Cell Value	="FL 3rd Tier"	text	NO
C9:C10,C1:C6	Cell Value	="FL 4th Tier"	text	NO
A1:A12	Expression	=XFC1048576="H"	text	NO

As always, any help appreciated

 

[Marvo]

I've made a little progress with this. I managed to change the formula on the new page and got it to work all except the HYPERLINKS in Column D. I changed the formula in those to something else to get the correct date. The box on the new sheet works but only to a certain extent. Sometimes NA will return or the date will be wrong. Strangely if I return to the older version to check, when I go back to the new version it has updated itself and is now correct, so it seems like the two boxes are linked in some way but I can't for the life of me see how? Obviously I don't want them connected, I want to delete the original but can't until I get the new box working independently.

OLD

Cobblers.xlsm
	CQ	CR	CS	CT
5559	CURRENT FORM
5560	GAMES	5
5561	LEAGUE	4
5562	ALL COMPETITIONS	4
5563
5564	BETTER		POINTS	DATE
5565	LEAGUE	$B$5550	5	12/03/2024
5566	ALL COMPETITIONS	$B$5550	5	12/03/2024
5567
5568	WORSE
5569	LEAGUE	$B$5551	2	16/03/2024
5570	ALL COMPETITIONS	$B$5551	2	16/03/2024
ALL

Cell Formulas
Range	Formula
CR5561	CR5561	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),(Alltable[Competition]=$CQ$5561)*(Alltable[Result]<>"")),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-ALL!$CR$5560)))
CR5562	CR5562	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),Alltable[Result]<>""),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-ALL!$CR$5560)))
CR5565	CR5565	=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5558>$CR$5561,ROW($AG$1:$AG5558)))*1,2)
CS5565	CS5565	=INDIRECT("AG"&MID($CR$5565,FIND("$",$CR$5565,2),7))
CT5565	CT5565	=HYPERLINK("#ALL!"&$CR$5565,TEXT(INDIRECT($CR$5565),"d/mm/yyyy"))
CR5566	CR5566	=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5558>$CR$5562,ROW($AF$1:$AF5558)))*1,2)
CS5566	CS5566	=INDIRECT("AF"&MID($CR$5566,FIND("$",$CR$5566,2),7))
CT5566	CT5566	=HYPERLINK("#ALL!"&$CR$5566,TEXT(INDIRECT($CR$5566),"d/mm/yyyy"))
CR5569	CR5569	=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5551<$CR$5561,ROW($AG$1:$AG5551)))*1,2)
CS5569	CS5569	=INDIRECT("AG"&MID($CR$5569,FIND("$",$CR$5569,2),7))
CT5569	CT5569	=HYPERLINK("#ALL!"&$CR$5569,TEXT(INDIRECT($CR$5569),"d/mm/yyyy"))
CR5570	CR5570	=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5551<$CR$5562,ROW($AF$1:$AF5551)))*1,2)
CS5570	CS5570	=INDIRECT("AF"&MID($CR$5570,FIND("$",$CR$5570,2),7))
CT5570	CT5570	=HYPERLINK("#ALL!"&$CR$5570,TEXT(INDIRECT($CR$5570),"d/mm/yyyy"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S1149:S1173,R1:R5560,CQ5559:CQ5570	Cell Value	="Northampton Town"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="Play-offs"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="League Trophy"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="League Cup"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="Div 3 South Cup"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="FA Cup"	text	NO
CT5567:CT5568,J1:J5560,CT5559:CT5564	Cell Value	="Charity Shield"	text	NO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564	Cell Value	="FL 3rd Tier"	text	NO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564	Cell Value	="FL 4th Tier"	text	NO
CQ5559:CQ5570	Expression	=E5561="H"	text	NO

NEW

Cobblers.xlsm
	A	B	C	D
1	CURRENT FORM
2	GAMES	5
3	LEAGUE	4
4	ALL COMPETITIONS	4
5
6	BETTER		POINTS	DATE
7	LEAGUE	$B$5550	5	12/03/2024
8	ALL COMPETITIONS	$B$5550	5	12/03/2024
9
10	WORSE
11	LEAGUE	$B$5551	2	16/03/2024
12	ALL COMPETITIONS	$B$5551	2	16/03/2024
Current Form

Cell Formulas
Range	Formula
B3	B3	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),(Alltable[Competition]=$A$3)*(Alltable[Result]<>"")),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-B2)))
B4	B4	=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),Alltable[Result]<>""),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-$B$2)))
B7	B7	=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5558>$B$3,ROW(ALL!$AG$1:$AG5558)))*1,2)
C7	C7	=INDIRECT("'ALL'!AG"&MID(B7,FIND("$",B7,2),7))
D7	D7	=LET(XL,XLOOKUP($C$7,ALL!$AG$2:$AG$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))
B8	B8	=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5558>$B$4,ROW(ALL!$AF$1:$AF5558)))*1,2)
C8	C8	=INDIRECT("'ALL'!AF"&MID($B$8,FIND("$",$B$8,2),7))
D8	D8	=LET(XL,XLOOKUP($C$8,ALL!$AF$2:$AF$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))
B11	B11	=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5551<$B$3,ROW(ALL!$AG$1:$AG5551)))*1,2)
C11	C11	=INDIRECT("'ALL'!AG"&MID($B$11,FIND("$",$B$11,2),7))
D11	D11	=LET(XL,XLOOKUP($C$11,ALL!$AG$2:$AG$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))
B12	B12	=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5551<$B$4,ROW(ALL!$AF$1:$AF5551)))*1,2)
C12	C12	=INDIRECT("'ALL'!AF"&MID($B$12,FIND("$",$B$12,2),7))
D12	D12	=LET(XL,XLOOKUP($C$12,ALL!$AF$2:$AF$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A12	Cell Value	="Northampton Town"	text	NO
D9:D10,D1:D6	Cell Value	="Play-offs"	text	NO
D9:D10,D1:D6	Cell Value	="League Trophy"	text	NO
D9:D10,D1:D6	Cell Value	="League Cup"	text	NO
D9:D10,D1:D6	Cell Value	="Div 3 South Cup"	text	NO
D9:D10,D1:D6	Cell Value	="FA Cup"	text	NO
D9:D10,D1:D6	Cell Value	="Charity Shield"	text	NO
C9:C10,C1:C6	Cell Value	="FL 3rd Tier"	text	NO
C9:C10,C1:C6	Cell Value	="FL 4th Tier"	text	NO
A1:A12	Expression	=XFC1048576="H"	text	NO