May 17 2022 05:55 AM
Hi all,
Have been trying to make a table where I can filter a few things. See image for example.
I want a specific thing in my table:
- If there is a red X in a row, for example Partner B has a Red X at 1.1, I only want to see that column and the max points. I want that the table automatticly hides the other partners. So for Partner B I only want to see 1.1, but when there is a 'x' in chapter 4.1. I also want to see that.
Its a complitcated table. I just need to know if there is a way where I can filter and hide the others columns that a spefic partner doen'st need to see.
Thanks!
May 17 2022 08:53 AM
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?
The reason I ask is that it may be simpler to restructure the table.
May 18 2022 12:15 AM
May 18 2022 12:26 PM
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.