match in grandchildren
I have a problem to which I can't find a solution. I hope you will be able to solve it.We have a store page where we collect data from parts from different departments. We then assign those parts to a project using a parent/child relationship. On the parent line, we use formulas like this one: =IF(AND(OR($[Engine type]378 = "CF34-3B"; $[Engine type]378 = "CF34-8C"); $Description378 = "Engine"); IF(ISERROR(MATCH("Brown Harness"; CHILDREN($Description378); 0)); "- Brown Harness"; ""); "")
The important part here is this one: IF(ISERROR(MATCH("Brown Harness"; CHILDREN($Description378); 0)); "- Brown Harness"; ""). This part is looking in the children's description and report a missing part (here the brown harness).
I have 1 column for each part and I am able to send the missing parts to the main column and have a list of all missing. This is working great so far.
The problem here is that when I have multiple tier. Here I could assign the brown harness to a kit and the kit to the main project. I now have parent/child/grandchild. The match can only search in the children and the grandchild is lost.
Do you know a way to be able to search in the grandchild tier? I have tried things like match(children(children())) but I received an error since the 2nd children send an array instead of a cell to the first one.
Thanks for your time and have a great day.