• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

TheExcelNoob

New Member
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:


1.png


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:

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

Note:

This is the formula on `Sheet 2 Results`Cell `A2`
Code:
`=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`
Code:
`=SUM(C2,B2)`
 
Hi TheExcelNoob, you do say this in your request:

and `Cell B & C & E` there is no formula only manual

So... That is the problem.

Care to upload your workbook (sample data)? Makes it easier to help you. But Perhaps make a second table to enter the scores. I guess TEAM A will play more then 1 match? So B and C should do a kind of SUM as well? And E also. There is a logic in giving points per result of a match I imagine. I do not directly understand the purpose of the status.
 
Hello GraH - Guido , thank you for your reply.

I made a sample of what am exactly working on, its actual workbook that am using in my work, but i modified it and removed the confidential data. The workbook is attached with this reply.. now the problem occurs in "Accounting" sheet when a business idea status between two launched B.Idea changes to launched in "Names" sheet.
What happens is for example, if in "Names" sheet the B.Idea status of "Lee Wilkerson" changes to "launched" , the name will appear on "Accounting" sheet and it will shift the name "Kenny Carpenter" below it , and it will take the data from the row , which is supposed to be for "Kenny Carpenter" not "Lee Wilkerson". So how can i prevent this ? i want each name to keep its data with it (entire row)
I hope its more clear now, with my actual work, not an example.
Thanks
 

Attachments

  • Sample.xlsx
    918.6 KB · Views: 6
TheExcelNoob

Firstly, Welcome to the Chandoo.org Forums

Your post has been cross-posted, which means it has been seen on other websites. This is considered poor practice, as it can waste peoples time, which could be spent elsewhere, especially if you get a solution and don't notify us.

I encourage you to please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/
 
TheExcelNoob

Firstly, Welcome to the Chandoo.org Forums

Your post has been cross-posted, which means it has been seen on other websites. This is considered poor practice, as it can waste peoples time, which could be spent elsewhere, especially if you get a solution and don't notify us.

I encourage you to please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/


Hello Hui,

Please accept my apologies, i didn't read site rules, I completely understand what you mean, but cross posting doesn't always mean I will leave my thread on other forums when i find the answer, actually am planning to post the solution to every single forum that i have posted and give credit to whom have helped me , I always do that.

Now the main reason why I posted this on this & other forums is , i don't usually do cross posting, but if you check my thread, i started a thread on stackoverflow 7 days ago and they did not help me and left me with no answer, personally any delay for my work is very bad for me, and I need to know If there is a possible solution so I can use ASAP, am desperately checking all of my threads on all forums each 30-40 min.


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 locations where my thread is posted : MrExcel , 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
 
Hello GraH - Guido , thank you for your reply.
I made a sample of what am exactly working on, its actual workbook that am using in my work, but i modified it and removed the confidential data.
I hope its more clear now, with my actual work, not an example.
Thanks
Thank you for the sample data. It has the same issue as the one I detected from your initial post.
Suggestion: why don't you add a couple of columns in Names to keep track of Loan amount and Returning?
And keep the overview in Accounting, but replace all columns with calculations.
As is attached file. Would that do?
 

Attachments

  • Sample.xlsx
    979.1 KB · Views: 3
The idea is : if M5 and M8 have selected "Launched", in "Lee Wikerson" M6 the dropdown list will not have "Launched" to select, thus can prevent "Lee Wilkerson" shown in the "Accounting" Sheet.

Try to set-up a "Adjusted Status List" in range AG9:AJ14

upload_2018-2-13_0-41-58.png

1] In AH10:AH14, the number to be filled as per your specified. (the number shown here only for demonstration purpose)

2] In AI10, formula copied down :

=IF(COUNTIF(M$4:M$100,AG10)<AH10,MAX(AI$9:AI9)+1,"")

3] In AJ10, formula copied down :

=IFERROR(INDEX(AG$10:AG$14,MATCH(ROW(A1),AI$10:AI$14,0)),"")

Then,

4] In M4 >> Data Validation >>

>> Allow : List

>> Source :

=OFFSET($AJ$10,,,COUNTIF($AJ$10:$AJ$14,"?*"))

and copied down

5] See attached file

Regards
Bosco
 

Attachments

  • DVSample(1).xlsx
    923.6 KB · Views: 3
Thank you for the sample data. It has the same issue as the one I detected from your initial post.
Suggestion: why don't you add a couple of columns in Names to keep track of Loan amount and Returning?
And keep the overview in Accounting, but replace all columns with calculations.
As is attached file. Would that do?

Thank you for your help, I think your suggestion is great, i haven't thought about it honestly... but the problem is that we have two different department working on this workbook , so i can't mix accounting department with the Information Dep. plus i have removed a lot of columns in "Names" sheet to make it simple, otherwise its really big(wide) sheet, the sample you uploaded is excellent this what exactly I need but I want the whole process to be in Accounting sheet. Would that be possible ?

I tried to understand what you did but its kinda complex for me, but I would like to know if its not possible , I wouldn't have problem If I add only column of "how much" which is in cell "AD3" to be in "Names" sheet and the other columns to filled and continued in Accounting sheet , if that is possible it would fix my problem.

Thank you again
 
The idea is : if M5 and M8 have selected "Launched", in "Lee Wikerson" M6 the dropdown list will not have "Launched" to select, thus can prevent "Lee Wilkerson" shown in the "Accounting" Sheet.

Try to set-up a "Adjusted Status List" in range AG9:AJ14

View attachment 49829

1] In AH10:AH14, the number to be filled as per your specified. (the number shown here only for demonstration purpose)

2] In AI10, formula copied down :

=IF(COUNTIF(M$4:M$100,AG10)<AH10,MAX(AI$9:AI9)+1,"")

3] In AJ10, formula copied down :

=IFERROR(INDEX(AG$10:AG$14,MATCH(ROW(A1),AI$10:AI$14,0)),"")

Then,

4] In M4 >> Data Validation >>

>> Allow : List

>> Source :

=OFFSET($AJ$10,,,COUNTIF($AJ$10:$AJ$14,"?*"))

and copied down

5] See attached file

Regards
Bosco

Hello bosco_yip,

Thank you for your reply, correct If am wrong , you added the "Adjusted Status List" to prevent "Lee Wikerson" to appear in "Accounting" sheet ? If so , i think you misunderstood my question , and am sorry If i wasn't clear.

I will try to explain what i exactly want. I actually want "Lee Wikerson" to be in "Accounting" sheet but what i don't want is that "Lee Wikerson" is taking data of "Kenny Carpenter" , which includes "How much" loan given and "how much returned" in months , these data are in "D5:R5". These data should be with "Kenny Carpenter".

What am going to do is that when new business idea status changes to "Launched" I will input new data for it manually when it appears in "Accounting" sheet, like in my case "Lee Wikerson".

I would suggest you to test what i exactly said and and you will see the problem.

I hope its clear now.

Thanks again
 
Last edited:
Thank you for your help, I think your suggestion is great, i haven't thought about it honestly... but the problem is that we have two different department working on this workbook , so i can't mix accounting department with the Information Dep. plus i have removed a lot of columns in "Names" sheet to make it simple, otherwise its really big(wide) sheet, the sample you uploaded is excellent this what exactly I need but I want the whole process to be in Accounting sheet.
I'm not sure want you require is possible with formulas (with VBA it would be). Once you start hard coding values they sit within the cells. These values then cannot respond to a calculation. That's why I suggested to move those columns.

But I understand a bit better what you are aiming for. Could this work. Allow both team to work on the same sheet. Have the wide table split in 2 parts, one for each group, with grouping?

Or.... having an intermediate list that highlights the missing names in the 2nd table.
Both suggestions are in the file.
Do-able?
 

Attachments

  • Sample_Grouping.xlsx
    961.7 KB · Views: 2
  • Sample_Grouping.xlsx
    961.7 KB · Views: 3
I'm not sure want you require is possible with formulas (with VBA it would be). Once you start hard coding values they sit within the cells. These values then cannot respond to a calculation. That's why I suggested to move those columns.

But I understand a bit better what you are aiming for. Could this work. Allow both team to work on the same sheet. Have the wide table split in 2 parts, one for each group, with grouping?

Or.... having an intermediate list that highlights the missing names in the 2nd table.
Both suggestions are in the file.
Do-able?

Could you please show me how to do it , or if possible, you do it in VBA for me? I would love to see that if its possible.

As for your suggestion, I think it would make it more complex & non organized sheet since we will have names on 2 columns in same sheet plus i tried to copy name and paste it in table In "Accounting" sheet and its copying the formula instead of the name. Instead we could try to copy names manually and complete the calculation(which is not preferred), but still I prefer your first method over this one.
 
TheExcelNoob
Could You use Filter with Your 'Accounting'-sheet?
..this is still without any VBA,
but with it ... there are much more possibilities (automatic refresh...).
(Ps. I cleaned some rows away ... that's why a little smaller file. )
 

Attachments

  • sample.xlsx
    50.7 KB · Views: 4
Actually the concept of Bosco could be the key. Make a drop down for the names in "accounting" and make this list dynamic in such way it does not contain the names already on the sheet "accounting".
And have like an alert in B1 saying "names need to be added" or "all names are listed".
For the alert I have an solution, but not for the dynamic drop down. Calling the Bosco's, Naranyan's, Chihiro's, John Jairo V's and others to the rescue.
 
TheExcelNoob
Could You use Filter with Your 'Accounting'-sheet?
..this is still without any VBA,
but with it ... there are much more possibilities (automatic refresh...).
(Ps. I cleaned some rows away ... that's why a little smaller file. )

Hello vletm,
Thank you for your excellent suggestion, I think its great and easy... though I have one problem, you changed the total budget with AGGREGATE function, which should be a fixed number, I can't modify this number since i want it to calculate how much is left , and leftover from it as well. But overall the idea is brilliant.
Thanks you so much!
 
TheExcelNoob
'Accounting'-sheet had 'many mystery and wrong formulas' and
that 'corner's the total budget also has something 'not correct'.
You can modify that 'corner' as needed or let me know what should there be.
'Corner' have to use aggregate-function with cells which are not fixed.
This version as 'automatic refresh for filtering' and so on...
Ideas? Questions?
 

Attachments

  • sample_TheExcelNoob.xlsb
    45.4 KB · Views: 3
Actually the concept of Bosco could be the key. Make a drop down for the names in "accounting" and make this list dynamic in such way it does not contain the names already on the sheet "accounting".
And have like an alert in B1 saying "names need to be added" or "all names are listed".
For the alert I have an solution, but not for the dynamic drop down. Calling the Bosco's, Naranyan's, Chihiro's, John Jairo V's and others to the rescue.

Do you mean like vletm did it for me? the filter option ?

Your first suggestion was absolutely great for me, what I did was I just hid the extra columns except "How much" that you added in "Names" Sheet, and that's it! now each department can work separately and with no problem.

Though am still curios, I want to know how can I do it with VBA ? because before I post here , I searched a lot for VBA and I knew its possible , but i didn't know what to search for? could you guide me ? If its possible you give me a hint what to search for , I would really appreciate it.

Thank you so much again for your help and time !
 
TheExcelNoob
'Accounting'-sheet had 'many mystery and wrong formulas' and
that 'corner's the total budget also has something 'not correct'.
You can modify that 'corner' as needed or let me know what should there be.
'Corner' have to use aggregate-function with cells which are not fixed.
This version as 'automatic refresh for filtering' and so on...
Ideas? Questions?

OMG , this is absolutely great , this auto refresh thing is awesome! Thank you!

For the total budget I can not modify it since you made it function, but I will explain how it works, I have one rule which is: I can give to each launched Business Idea alone (600€). So If you check my original sample, the "Left Over" in "Accounting" sheet calculation is based on this principle, which means for example If i give for 1st Business Idea 500€ i will have 100€ "left over", and from total budget 64,500€ "Left" plus sum of "returned". The total budget should be a fixed number and its "65000€" and can not be modified. could you do it based on these ?

Thanks
 
TheExcelNoob
... and of course You used other values than Your sample ...
I added that 'Business Idea Alone' value above 'How Much?'.
This do not take care if someone would use over that value with 'How Much?'
'Top Right'-corner tried to modify as You hoped.
(ps. That 'green blink' shows 'filter refreshing')
 

Attachments

  • sample_TheExcelNoob.xlsb
    46.5 KB · Views: 2
TheExcelNoob
... and of course You used other values than Your sample ...
I added that 'Business Idea Alone' value above 'How Much?'.
This do not take care if someone would use over that value with 'How Much?'
'Top Right'-corner tried to modify as You hoped.
(ps. That 'green blink' shows 'filter refreshing')

vletm,
Thank you so much, currently am using the version you made in my work, am not gonna lie, it took hours to modify and use same VBA data that you did, for my actual file...

The auto refreshing+filter thing was fantastic idea , it works like a charm, this is what i exactly want.

Thanks again , appreciate your help.
 
Back
Top