1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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?

Discussion in 'Ask an Excel Question' started by TheExcelNoob, Feb 12, 2018.

  1. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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 (vb):
    `=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 (vb):
    `=SUM(C2,B2)`
  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    857
    Hi TheExcelNoob, you do say this in your request:

    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.
  3. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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

    Attached Files:

  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    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/
  5. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10

    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
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    857
    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?

    Attached Files:

    TheExcelNoob likes this.
  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    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

    Attached Files:

    TheExcelNoob likes this.
  8. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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
  9. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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: Feb 13, 2018
  10. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    857
    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?

    Attached Files:

    TheExcelNoob likes this.
  11. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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.
  12. vletm

    vletm Excel Ninja

    Messages:
    4,421
    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. )

    Attached Files:

    TheExcelNoob likes this.
  13. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    857
    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 likes this.
  14. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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!
  15. vletm

    vletm Excel Ninja

    Messages:
    4,421
    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?

    Attached Files:

    TheExcelNoob likes this.
  16. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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 !
  17. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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
  18. vletm

    vletm Excel Ninja

    Messages:
    4,421
    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')

    Attached Files:

    TheExcelNoob likes this.
  19. TheExcelNoob

    TheExcelNoob New Member

    Messages:
    10
    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.
    vletm likes this.

Share This Page