How to make entire row connected together ?

[ExcelLAd]

Hello guys,

So 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`does is that each game that has launched status on `Teams` it gets on `Results` sheet and then`Results` I 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, If you confused or did not get my question , here is my example it will be more clear:

Sheet1: Teams
​

[TABLE="class: cms_table_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"]Names[/TD]
[TD="align: center"]Status[/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="class: cms_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"]Names[/TD]
[TD="align: center"]1st Half goals[/TD]
[TD="align: center"]2nd Half[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Points[/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]


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&data of TEAM D. Like this:


Sheet2: Results (The problem)

[TABLE="class: cms_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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: left"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]TEAM D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[/TR]
</tbody>[/TABLE]


So what can I do to make TEAM D keep the data of entire row while TEAM B shifts it ??
Please help

 

[Coding4Fun]

How are you shifting Team D down and inserting Team B into the results sheet?

 

[ExcelLAd]

Hello @Coding4Fun , am using formula to do this automatically.
This is the formula on <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Sheet 2 Results</code> Cell <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">A2</code>

[COLOR=#242729][FONT=Consolas]=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))),"")[/FONT][/COLOR]

and <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Cell B & C & E</code> there is no formula only manual input, and for Cell D :

[COLOR=#242729][FONT=Consolas]=SUM(C2,B2)[/FONT][/COLOR]

So when status of TEAM B changes from Pending in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Sheet1</code><code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Teams </code>to launched it appears in Sheet 2 Results automatically, it shifts TEAM D below it and takes the data of TEAM D

If you know how to fix this please let me know, been searching for it for a week now...

Thanks.

 

[ExcelLAd]

Hello,

I can't seem to find how to edit my thread, so i will post it here , if mod can update it for me i would be thankful.
These are the forums where my thread is posted as well : Chandoo , Excelforum and Stackoverflow (which was the main reason behind why I posted on other forums)
now you can see where my threads are, hopefully you would be able to help.

sorry for inconvenience again.
Thanks

 

[ExcelLAd]

I found an answer for my question, thanks to GraH - Guido & Vletm users on Chandoo Forum, who helped me find the answer.
I found 2 answers both helpful and working for my case, you can find them here and here
Thanks.

 

[Coding4Fun]

I am glad you found a solution and thanks for sharing.