Forum Discussion
Excel Table
1. Energy consultancy
2. External partners (like architects)
3. The points represent certain requirements that must be met. However, each party has its own responsibilities.
4. Its a long-term project. Its a general table which we can use for different projects.
5. Yes, we want to give the same workbook to each partner. The columns don't need to be locked. I want to give them the opportunity to hide the columns, so that they only can see their responsibilities ( but also still the whole overview)
I think there are many ways to interpret what you're asking so I offer one solution based on my understanding that you want to:
a) provide a way to simplify the view for a partner
b) show only "completed" groups of tasks (indicated by an x in the parent row for those tasks)
So, I propose a "Select partner" cell with data validation set to the range containing the partner names:
And a formula to filter the main table based on the selected partner (in cell K1 in the attachment):
=LET(
data,$A$1:$G$16,
h,INDEX(data,1,),
colfilt,FILTER(data,(h=$I$1)+(h="Tasks")+(h="Max points")),
rowhead,INDEX(data,,1),
btaskparents,LEN(rowhead)=3,
taskparents,FILTER(rowhead,(btaskparents)*(INDEX(colfilt,,2)="x")),
bgroups,SCAN(FALSE,LEFT(rowhead,3),LAMBDA(a,b,NOT(ISNA(XMATCH(b,taskparents))))),
rowfilt,FILTER(colfilt,(bgroups)+(rowhead="Tasks")),
out,CHOOSE({1,2,3},T(INDEX(rowfilt,,1)),T(INDEX(rowfilt,,2)),INDEX(rowfilt,,3)&""),
out
)You can of course hide columns A through G permanently, or move the filtered list and the drop-down to another sheet.
Note that if the task name on the parent row (grey row) is longer than three characters, you would need to change the definition of the "btaskparents" variable and the "bgroups" variable (inside the LEFT function) appropriately.
- It worked, thanks a lot!