miltilevel Bill of Material

I don't know what you want. By the way, valid can't refresh automatic without VBA.
I think you must ask one question in one post, don't drip-feeding your qestion.

you mean for the same file i should post different posts?
if you can check the file that I have attached V4. it is the same your formula, but I want to exclude "Non-Solvent" from the formula; is it possible?

Try this,

=LET(
  a,CHOOSECOLS(FILTER(Re,Re[Components]=Details!D2),2,3,4,8,6),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(m,n,VSTACK(m,IF(INDEX(a,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(a,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(a,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(a,n,))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)),LAMBDA(m,n,VSTACK(m,IF(INDEX(b,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(b,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(b,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(b,n,))))),1),
  d,DROP(REDUCE("",SEQUENCE(ROWS(c)),LAMBDA(m,n,VSTACK(m,IF(INDEX(c,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(c,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(c,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(c,n,))))),1),
  e,DROP(REDUCE("",SEQUENCE(ROWS(d)),LAMBDA(m,n,VSTACK(m,IF(INDEX(d,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(d,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(d,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(d,n,))))),1),
  f,SORT(UNIQUE(INDEX(e,,4))),
  g,FILTER(f,INDEX(f,,1)<>"Non-Solvent"),
  IFERROR(DROP(REDUCE("",g,LAMBDA(m,n,VSTACK(m,HSTACK(n,SUM((INDEX(e,,4)=n)*(INDEX(e,,2))))))),1),"")
)

What I mean is that you should tell all the problems at the beginning post.

This is working . The total percentage can be added? Since if I add in a different formula and if the table size expands, I will get an error.
Honestly, when I started this thread, I didn't expect someone to have this knowledge, and my idea was expanded.

Try this,

=LET(
  a,CHOOSECOLS(FILTER(Re,Re[Components]=Details!D2),2,3,4,8,6),
  b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(m,n,VSTACK(m,IF(INDEX(a,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(a,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(a,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(a,n,))))),1),
  c,DROP(REDUCE("",SEQUENCE(ROWS(b)),LAMBDA(m,n,VSTACK(m,IF(INDEX(b,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(b,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(b,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(b,n,))))),1),
  d,DROP(REDUCE("",SEQUENCE(ROWS(c)),LAMBDA(m,n,VSTACK(m,IF(INDEX(c,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(c,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(c,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(c,n,))))),1),
  e,DROP(REDUCE("",SEQUENCE(ROWS(d)),LAMBDA(m,n,VSTACK(m,IF(INDEX(d,n,3)="Assembly",LET(z,CHOOSECOLS(FILTER(Re,Re[Components]=INDEX(d,n,1)),2,3,4,8,6),HSTACK(INDEX(z,,1),INDEX(z,,2)*INDEX(d,n,2),INDEX(z,,3),INDEX(z,,4),INDEX(z,,5))),INDEX(d,n,))))),1),
  f,SORT(UNIQUE(INDEX(e,,4))),
  g,FILTER(f,INDEX(f,,1)<>"Non-Solvent"),
  h,IFERROR(DROP(REDUCE("",g,LAMBDA(m,n,VSTACK(m,HSTACK(n,SUM((INDEX(e,,4)=n)*(INDEX(e,,2))))))),1),""),
  VSTACK(h,HSTACK("",SUM(INDEX(h,,2))))
)

worked, but we need to change the remaining 3 formulas to have the same total percentages. i highlighted them in the attached file.