Parts per based on BOM tree|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I have been asked by a colleague to help calculate a parts per device for a BOM worksheet we use. For whatever reason, we cannot or will not convert the data to a table which would make this much easier. We also cannot use a macro, which would trivialize this. We have 8 BOM levels (0-7). We need the parts per device to calculate by working backwards through the BOM tree. BOM Tree is columns G-N. Parts per device is in column AI.

Using row 63 as a test, I was able to get the starting quantity with the following formula

=INDIRECT(ADDRESS(CELL("row",INDEX(G63:N63,MATCH(FALSE,ISBLANK(G63:N63),0))),CELL("col",INDEX(G63:N63,MATCH(FALSE,ISBLANK(G63:N63),0)))))

and I can get to the next highest cell in the tree and multiply those 2 values by doing the following:

fv is the last non-blank cell in the range we are looking into (since there is only 1 value per line in the BOM tree)
vr is the row where the first value is found
vc is the column where the first value is found
v is the value we are starting with(2)
c is the column to the left of where v is located
start is the first cell of our tree values in that row
end is the last cell of our range (1 row higher than where our original value was found)

=LET(
fv,INDEX(G63:N63,MATCH(FALSE,ISBLANK(G63:N63),0)),
vr,CELL("row",fv),
vc,CELL("col",fv),
v,INDIRECT(ADDRESS(vr,vc)),
c,vc-1,
start,INDIRECT(ADDRESS(8,c)),
end,INDIRECT(ADDRESS(vr-1,c)),
v * INDIRECT(ADDRESS(CELL("row",INDEX(start:end,MAX((start:end<>"")*(ROW(start:end)))-7)),c))
)

The second value just happens to be on the next row up but it could potentially be several rows up. This gets me the first calculation but there are 5 more (potentially 6) columns to parse. This is already getting very complex for my skillset. Am I overlooking something that would make this easier? Can anyone help me finish this? I have attached a workbook. Thank you.