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

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)))

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

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

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!

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.

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!

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!

I think I have it fixed for you to try.