Q: How do i keep a row connected together(moving together) while shifting/adding row above it?

ExcelLAd

New Member
Joined
Feb 5, 2018
Messages
9
I have an Excel file containing two sheets:

- `Teams` (which contains details of teams and time schedule of their matches)


- `Results` (contains calculation and number of matches and results, etc).


What`Results`did is that each game that has launched status on `Teams` it gets on `Results` sheet and then`Results`input the number and calculation on that sheet.


My problem is that when there is a game inside two started games, when it starts the new name comes and shifts the name row below it and keeps the details of the row below it for itself, while it should be to next of it, here is my example:




**`Sheet1` `Teams`**
-


A B
1 **Names** **Status**


2 TEAM A Launched


3 TEAM B Pending


4 TEAM C Pending


5 TEAM D Launched






**2 `Results`**
-

A B C D E
1 **Names** **1st Half goals** **2nd half** **total** **points**


2 TEAM A 1 2 3 13


3 TEAM D 3 1 4 10



So what happens here is that if change the status of TEAM B on `Sheet1` of `Teams` it will be appear on 2 `Results` and it will take the row input of TEAM D.


It will be like this:




**2 `Results`**
-

A B C D E
1 **Names** **1st Half goals** **2nd half** **total** **points**


2 TEAM A 1 2 3 13


3 TEAM B 3 1 4 10


4 TEAM D



Is there any solution to keep entire row moving together? please let me know.


This is the formula on `Sheet 2 Results` Cell `A2`
`=IFERROR(INDEX(TEAMS!A$2:A$550,SMALL(IF(TEAMS!B$2:B$550="Launched",ROW(TEAMS!B$2:B$550)-ROW(TEAMS!B$1)),ROW(TEAMS!B2))),"")`


and `Cell B & C & E` there is no formula only manual input, and for `Cell D` `=SUM(C2,B2)`
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Am sorry i didn't notice that i have clicked on code and the thread is like that ... i can't find out how to edit it ? is it possible here or no ?
 
Upvote 0
I guess its not clear that's why no one is replying, so I made the example clearer for you guys , so you can help:


Sheet1: Teams




[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"][FONT=&quot]Names[/FONT][/TD]
[TD="align: center"][FONT=&quot]Status[/FONT][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]TEAM A[/TD]
[TD="align: center"]Launched[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]TEAM B[/TD]
[TD="align: center"]Pending[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]TEAM C[/TD]
[TD="align: center"]Pending[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]TEAM D[/TD]
[TD="align: center"]Launched[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2: Results



[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"][FONT=&quot]Names[/FONT][/TD]
[TD="align: center"][FONT=&quot]1st Half goals[/FONT][/TD]
[TD="align: center"][FONT=&quot] 2nd Half[/FONT][/TD]
[TD="align: center"][FONT=&quot]Total[/FONT][/TD]
[TD="align: center"][FONT=&quot]Points[/FONT][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]TEAM A[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"] 13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]TEAM D[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody>[/TABLE]


The problem occurs here , when a game between two started games, starts and shifts down the name to down , and gets the details of the row below it instead of being empty ...

Sheet2: Results (The problem)
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: left"]1[/TD]
[TD]Names[/TD]
[TD]1st Half Goalss[/TD]
[TD]2nd Half[/TD]
[TD]Total[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD="align: left"]2
[/TD]
[TD]TEAM A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD="align: left"]3[/TD]
[TD]TEAM B[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: left"]4[/TD]
[TD]TEAM D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Please help !
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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