On one sheet Total Value, I have a list of assemblies in A2:A21. In the column headers B1:BA1, I have the 52 weeks of the year enumerated. I need to populate this matrix with the total value of components used for each assembly in each week.

For each assembly 1,2...20, there is a sheet with a corresponding name (i.e.,'1','2'...'20') that contains in Column A (N!A2:...) the list of components (IDs) and in Column B (N!B1:...) the quantity of that component needed for the assembly.

Additionally, I have another sheet CV which contains a table of all components' values by weeks - CV!A2:A200 containing the component IDs, and CV!B1:BA1 enumerating the weeks.

I am trying to come up with a dynamic formula for each Assembly/Week intersection in Total Value that will SUMPRODUCT the quantity of each component needed (from the corresponding assembly sheet) by the value of those components in the given week, given in CV.

If there were just one assembly, and CV contained only the components needed for that assembly, this would be a simple SUMPRODUCT. Array 1 would contain the component quantities for the assembly, and Array 2 would contain the components' values in that week. In my case, CV is a long list in no particular order. I need the second array in the SUMPRODUCT to lookup precisely the values of the components in Array 1 for that week.

I don't even know how to start on this one. Any ideas?

CodePudding user response：

HI try this formula in cell 'Total Value'!B2 :

=SUMPRODUCT(IFNA(VLOOKUP(INDIRECT("'"&$A2&"'!A2:A200"),CV!$A$1:$BA$200,B$1 1,0),0),INDIRECT("'"&$A2&"'!B2:B200"))

It should work if I understand you correctly