Moving cells on to another worksheet

M

Marvo

Board Regular
Joined
Nov 27, 2023
Messages
155
Office Version
  1. 2021
Platform
  1. Windows
HI,

I have the following on a worksheet

Cobblers.xlsm
CQCRCSCT
5559CURRENT FORM
5560GAMES12
5561LEAGUE13
5562ALL COMPETITIONS13
5563
5564BETTERPOINTSDATE
5565LEAGUE$B$5549149/03/2024
5566ALL COMPETITIONS$B$5549149/03/2024
5567
5568WORSE
5569LEAGUE$B$55511016/03/2024
5570ALL COMPETITIONS$B$55511016/03/2024
ALL
Cell Formulas
RangeFormula
CR5561CR5561=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)))
CR5562CR5562=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)))
CR5565CR5565=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5558>$CR$5561,ROW($AG$1:$AG5558)))*1,2)
CS5565CS5565=INDIRECT("AG"&MID($CR$5565,FIND("$",$CR$5565,2),7))
CT5565CT5565=HYPERLINK("#ALL!"&$CR$5565,TEXT(INDIRECT($CR$5565),"d/mm/yyyy"))
CR5566CR5566=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5558>$CR$5562,ROW($AF$1:$AF5558)))*1,2)
CS5566CS5566=INDIRECT("AF"&MID($CR$5566,FIND("$",$CR$5566,2),7))
CT5566CT5566=HYPERLINK("#ALL!"&$CR$5566,TEXT(INDIRECT($CR$5566),"d/mm/yyyy"))
CR5569CR5569=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5551<$CR$5561,ROW($AG$1:$AG5551)))*1,2)
CS5569CS5569=INDIRECT("AG"&MID($CR$5569,FIND("$",$CR$5569,2),7))
CT5569CT5569=HYPERLINK("#ALL!"&$CR$5569,TEXT(INDIRECT($CR$5569),"d/mm/yyyy"))
CR5570CR5570=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5551<$CR$5562,ROW($AF$1:$AF5551)))*1,2)
CS5570CS5570=INDIRECT("AF"&MID($CR$5570,FIND("$",$CR$5570,2),7))
CT5570CT5570=HYPERLINK("#ALL!"&$CR$5570,TEXT(INDIRECT($CR$5570),"d/mm/yyyy"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S1149:S1173,R1:R5560,CQ5559:CQ5570Cell Value="Northampton Town"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="Play-offs"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="League Trophy"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="League Cup"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="Div 3 South Cup"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="FA Cup"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="Charity Shield"textNO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564Cell Value="FL 3rd Tier"textNO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564Cell Value="FL 4th Tier"textNO
CQ5559:CQ5570Expression=E5561="H"textNO


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
ABCD
1CURRENT FORM
2GAMES12
3LEAGUE13
4ALL COMPETITIONS13
5
6BETTERPOINTSDATE
7LEAGUE$B$554900/01/1900
8ALL COMPETITIONS$B$554900/01/1900
9
10WORSE
11LEAGUE$B$555800/01/1900
12ALL COMPETITIONS$B$555800/01/1900
Current Form
Cell Formulas
RangeFormula
B3B3=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)))
B4B4=LET(s,FILTER(SWITCH(Alltable[Result],"W",3,"D",1,"L",0),Alltable[Result]<>""),c,COUNTA(s),SUM(FILTER(s,SEQUENCE(c)>c-B2)))
B7B7=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5558>B3,ROW(ALL!$AG$1:$AG5558)))*1,2)
C7,C11C7=INDIRECT("AG"&MID(B7,FIND("$",B7,2),7))
D7:D8,D11:D12D7=HYPERLINK("#ALL!"&B7,TEXT(INDIRECT(B7),"d/mm/yyyy"))
B8B8=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5558>B4,ROW(ALL!$AF$1:$AF5558)))*1,2)
C8,C12C8=INDIRECT("AF"&MID(B8,FIND("$",B8,2),7))
B11B11=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5558<B3,ROW(ALL!$AG$1:$AG5558)))*1,2)
B12B12=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5558<B4,ROW(ALL!$AF$1:$AF5558)))*1,2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A12Cell Value="Northampton Town"textNO
D9:D10,D1:D6Cell Value="Play-offs"textNO
D9:D10,D1:D6Cell Value="League Trophy"textNO
D9:D10,D1:D6Cell Value="League Cup"textNO
D9:D10,D1:D6Cell Value="Div 3 South Cup"textNO
D9:D10,D1:D6Cell Value="FA Cup"textNO
D9:D10,D1:D6Cell Value="Charity Shield"textNO
C9:C10,C1:C6Cell Value="FL 3rd Tier"textNO
C9:C10,C1:C6Cell Value="FL 4th Tier"textNO
A1:A12Expression=XFC1048576="H"textNO


As always, any help appreciated
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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
CQCRCSCT
5559CURRENT FORM
5560GAMES5
5561LEAGUE4
5562ALL COMPETITIONS4
5563
5564BETTERPOINTSDATE
5565LEAGUE$B$5550512/03/2024
5566ALL COMPETITIONS$B$5550512/03/2024
5567
5568WORSE
5569LEAGUE$B$5551216/03/2024
5570ALL COMPETITIONS$B$5551216/03/2024
ALL
Cell Formulas
RangeFormula
CR5561CR5561=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)))
CR5562CR5562=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)))
CR5565CR5565=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5558>$CR$5561,ROW($AG$1:$AG5558)))*1,2)
CS5565CS5565=INDIRECT("AG"&MID($CR$5565,FIND("$",$CR$5565,2),7))
CT5565CT5565=HYPERLINK("#ALL!"&$CR$5565,TEXT(INDIRECT($CR$5565),"d/mm/yyyy"))
CR5566CR5566=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5558>$CR$5562,ROW($AF$1:$AF5558)))*1,2)
CS5566CS5566=INDIRECT("AF"&MID($CR$5566,FIND("$",$CR$5566,2),7))
CT5566CT5566=HYPERLINK("#ALL!"&$CR$5566,TEXT(INDIRECT($CR$5566),"d/mm/yyyy"))
CR5569CR5569=ADDRESS(LOOKUP(9^99,IF($AG$1:$AG5551<$CR$5561,ROW($AG$1:$AG5551)))*1,2)
CS5569CS5569=INDIRECT("AG"&MID($CR$5569,FIND("$",$CR$5569,2),7))
CT5569CT5569=HYPERLINK("#ALL!"&$CR$5569,TEXT(INDIRECT($CR$5569),"d/mm/yyyy"))
CR5570CR5570=ADDRESS(LOOKUP(9^99,IF($AF$1:$AF5551<$CR$5562,ROW($AF$1:$AF5551)))*1,2)
CS5570CS5570=INDIRECT("AF"&MID($CR$5570,FIND("$",$CR$5570,2),7))
CT5570CT5570=HYPERLINK("#ALL!"&$CR$5570,TEXT(INDIRECT($CR$5570),"d/mm/yyyy"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S1149:S1173,R1:R5560,CQ5559:CQ5570Cell Value="Northampton Town"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="Play-offs"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="League Trophy"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="League Cup"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="Div 3 South Cup"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="FA Cup"textNO
CT5567:CT5568,J1:J5560,CT5559:CT5564Cell Value="Charity Shield"textNO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564Cell Value="FL 3rd Tier"textNO
H2:H677,CS5567:CS5568,I1:I5560,CS5559:CS5564Cell Value="FL 4th Tier"textNO
CQ5559:CQ5570Expression=E5561="H"textNO


NEW

Cobblers.xlsm
ABCD
1CURRENT FORM
2GAMES5
3LEAGUE4
4ALL COMPETITIONS4
5
6BETTERPOINTSDATE
7LEAGUE$B$5550512/03/2024
8ALL COMPETITIONS$B$5550512/03/2024
9
10WORSE
11LEAGUE$B$5551216/03/2024
12ALL COMPETITIONS$B$5551216/03/2024
Current Form
Cell Formulas
RangeFormula
B3B3=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)))
B4B4=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)))
B7B7=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5558>$B$3,ROW(ALL!$AG$1:$AG5558)))*1,2)
C7C7=INDIRECT("'ALL'!AG"&MID(B7,FIND("$",B7,2),7))
D7D7=LET(XL,XLOOKUP($C$7,ALL!$AG$2:$AG$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))
B8B8=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5558>$B$4,ROW(ALL!$AF$1:$AF5558)))*1,2)
C8C8=INDIRECT("'ALL'!AF"&MID($B$8,FIND("$",$B$8,2),7))
D8D8=LET(XL,XLOOKUP($C$8,ALL!$AF$2:$AF$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))
B11B11=ADDRESS(LOOKUP(9^99,IF(ALL!$AG$1:$AG5551<$B$3,ROW(ALL!$AG$1:$AG5551)))*1,2)
C11C11=INDIRECT("'ALL'!AG"&MID($B$11,FIND("$",$B$11,2),7))
D11D11=LET(XL,XLOOKUP($C$11,ALL!$AG$2:$AG$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))
B12B12=ADDRESS(LOOKUP(9^99,IF(ALL!$AF$1:$AF5551<$B$4,ROW(ALL!$AF$1:$AF5551)))*1,2)
C12C12=INDIRECT("'ALL'!AF"&MID($B$12,FIND("$",$B$12,2),7))
D12D12=LET(XL,XLOOKUP($C$12,ALL!$AF$2:$AF$5558,Alltable[DATE],,0,-1),HYPERLINK(CELL("address",XL),XL))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A12Cell Value="Northampton Town"textNO
D9:D10,D1:D6Cell Value="Play-offs"textNO
D9:D10,D1:D6Cell Value="League Trophy"textNO
D9:D10,D1:D6Cell Value="League Cup"textNO
D9:D10,D1:D6Cell Value="Div 3 South Cup"textNO
D9:D10,D1:D6Cell Value="FA Cup"textNO
D9:D10,D1:D6Cell Value="Charity Shield"textNO
C9:C10,C1:C6Cell Value="FL 3rd Tier"textNO
C9:C10,C1:C6Cell Value="FL 4th Tier"textNO
A1:A12Expression=XFC1048576="H"textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
LET Formula Enhancement
Replies
2
Views
114
Fonzie
F
M
  • Question
Recursive LAMBDA for progressive SMALL calculations
Replies
1
Views
80
macfuller
M
JenniferMurphy
  • Question
Is there a Null function?
Replies
6
Views
215
JenniferMurphy
JenniferMurphy
G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
347
Greenbehindthecells
G
T
  • Question
SUMIFS or SUMPRODUCT?
Replies
1
Views
229
awoohaw
awoohaw

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top