+ Reply to Thread
Results 1 to 17 of 17

Change "Parent" cell based on results of multiple "Children" cells

  1. #1
    Registered User
    Join Date
    10-09-2012
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2020
    Posts
    45

    Change "Parent" cell based on results of multiple "Children" cells

    Hi everyone,

    I've been thinking about this for a while and can't determine a viable solution. I have a list of data that has varying amounts of "Children" per each "Parent". An example is that the parents all end in .0 (i.e. 1.0, 2.0, 3.0) and if the values in col D that equate to that parent (i.e. 1.1, 1.2, 1.3, 1.4, etc.) = "Completed", then the parent (1.0) = "Approved". The only issue I seem to be having is that the amount of "children" could vary between each one and could potentially have none. Any thoughts would be appreciated and I have attached an example of what I'm working on. Thanks in advance for any help!

    ParentChildExample.xlsx
    Attached Files Attached Files

  2. #2
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Looks like your first AND statement is missing a piece...

    IF(AND(D3="*.0",....???....),"To Be Approved",IF(AND(E3<>"",F3=""),"In Progress",IF(AND(E3="",F3<>""),"Completed",0)))

  3. #3
    Registered User
    Join Date
    10-09-2012
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2020
    Posts
    45

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Sorry, Steve. I accidentally uploaded the version with formulas that I was working on. That's the part I'm having issues with...I have uploaded one with a simplified version of the text values I'm looking to include...

    ParentChildExample_v1.xlsx

  4. #4
    Registered User
    Join Date
    09-17-2011
    Location
    Prattville, Alabama
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Change "Parent" cell based on results of multiple "Children" cells

    I may have a solution but have not learned to use this forum. I reworked a formula and placed it in your example and renamed it. I am trying to figure how to attach it to return it to you.....

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Can you please explain why G9 cell is shown blank?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  6. #6
    Registered User
    Join Date
    10-09-2012
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2020
    Posts
    45

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Sure thing. Cell G9 is shown as blank because cells G12/G13 (2.3/2.4) are not completed, therefore, the parent (2.0), can't be complete. Hope that clears things up and let me know if there are any other questions. Thanks!

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Before releasing the formula last and final question. What is the purpose of Column-E "Y"? whether it is related to this logic?

  8. #8
    Registered User
    Join Date
    10-09-2012
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2020
    Posts
    45

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Hi Sixthsense - colE was used as a helper column for another variable of "In Progress". For colG, part of the formula would be if(AND(E4="Y",F4<>""),"In Progress",..do something else... Hopefully that makes sense. Basically, if it's In Progress, it can't have a "Completed Date" and vice versa.

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Change "Parent" cell based on results of multiple "Children" cells

    G9 is shown as blank because cells G12/G13 (2.3/2.4) are not completed, therefore, the parent (2.0), can't be complete
    About the above quote, then why G3 is marked as Approved if we apply the above logic then G3 should be blank right?

  10. #10
    Registered User
    Join Date
    10-09-2012
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2020
    Posts
    45

    Re: Change "Parent" cell based on results of multiple "Children" cells

    No, the logic is below the number. So 1.0 is what needs to change based on if 1.1 - 1.5 all = Completed. This would be the same for 2.0 if 2.1 - 2.4 all equal Completed, but since G12 & G13 are both blank, 2.0 would not be Completed. Hopefully that clears up any confusion and thanks for all the help in advance!

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Change "Parent" cell based on results of multiple "Children" cells

    but since G12 & G13 are both blank, 2.0 would not be Completed
    I am totally confused

    Why you are talking about Column-G values needs to be included in the logic when we are trying to get the Output in Column-G?

  12. #12
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: Change "Parent" cell based on results of multiple "Children" cells

    and another question are you manually entering the "Completed" to the children

    i think it is count -- count the number of Completed (in a child) if no blank display "Approved" in Parent is that it, so formula are only in the Column G Parent rows????
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Please dont ask me the logic of the below formula, because I am not sure whether this covers your expected logic's, Just my formula workout which can be fine tuned if you explain your logic's.

    In G3 Cell

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag it down...

  14. #14
    Registered User
    Join Date
    10-09-2012
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2020
    Posts
    45

    Re: Change "Parent" cell based on results of multiple "Children" cells

    Sixthsense - Thanks for taking the time, but unfortunately, that does not cover the expected result. I have suggested a few other ways to have this data setup, but the client wants, what the client wants...

    I'll try to reexplain it slightly different to hopefully make some more sense to those who aren't as close to it...This is a task list and if all the tasks under the main task are completed, then the main task should be switched to "Approved".

    In colE, I will be manually inputting "Y" if the task is "In Progress", and in colF, will be manually inputting the date the task was completed. The first two parts of the equation I will be typing in colF, I can get where I will be saying =if(and(E4="Y",F4=""),"In Progress", if(and(E4="",F4<>""),"Completed" - Hopefully that clears up part A)

    Part B) is where I'm having issues and don't really know how to attack it (helper columns are also allowed if necessary)...In colA, I have the main number of the Task (Rows 3-8 = 1, 9-13 = 2, etc.). colB is used to detail what sub-task it is of the main task, so C4:C8 are all sub-tasks of C3, and C10:C13 are sub-tasks of C9. What I would like to happen is if all the sub-tasks = "Completed" (due to the formula above), it will switch the main task, "parent", to "Approved". Another issue, not sure how big or small is I don't know how many sub-tasks each main task will have.

    Hope that helps to clear up any confusion and thanks for all the help from everyone thus far!

  15. #15
    Registered User
    Join Date
    09-17-2011
    Location
    Prattville, Alabama
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Change "Parent" cell based on results of multiple "Children" cells

    I think I have it fixed for you to try.
    Attached Files Attached Files

  16. #16
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Change "Parent" cell based on results of multiple "Children" cells

    In G3 cell

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag it down...

    Edit: Ignore this one This is not the formula which I wanted to post. By mistake Posted the Old formula, Not even saved my new formula so again I have to rebuild it
    Last edited by :) Sixthsense :); 12-14-2012 at 02:21 AM. Reason: Ignore This Post Wrongly Posted

  17. #17
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Change "Parent" cell based on results of multiple "Children" cells

    The below was the formula I wanted to post earlier...

    In G3 cell

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1