Forum Discussion
Excel Table
I think there are a few ways to solve this, but to make sure we land on the one that's most appropriate for your situation, can you provide some background about the purpose of this?
- What kind of business is this?
- Who are these "partners"? Are they employees? Something else?
- What do the points represent?
- Is this a long-term project (months to years) or something that will be used for a short period (weeks to a few months)?
- Do you intend to give the same workbook to each partner? Related: will the hidden columns be locked so that the partner can't unhide them to see the other partners?
The reason I ask is that it may be simpler to restructure the 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!