Forum Discussion
Providing info on tab, based on other tabs with parent child relationship.
Hi, This post/question is based on a simplified verion of a security-matrix that is te be audited in the company and also audited against the real time security-tables in external system. I changed ...
I think it is the 'double lookup' that you are having an issue with. I did it using both structured references (tables) and normal workbook references:
structured reference needs to be adjusted for each column
=IF(SUM((TRANSPOSE(FunctionRole_matrix[@[Sweep]:[task-z]])="x")*(TaskTool_matrix[Broom]="x")),"x","")
normal sheet ranges need to be copied down the rows
=IF(MMULT(EXPAND(1,1,ROWS($A$31:$A$37),1),(TRANSPOSE(F11:L11)="x")*($B$31:$H$37="x")),"x","")
that said you can use MAKEARRAY or REDUCE or THUNKs with LAMBDAs to make them all in 1 formula
Here are the results:
I will try to attach the sample file if this system lets me