Excel Table

Occasional Contributor

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.  

 

BeyK40_0-1652791586200.png

 

Thanks! 

 

4 Replies

@BeyK40 

 

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?

 

  1. What kind of business is this?
  2. Who are these "partners"? Are they employees? Something else?
  3. What do the points represent?
  4. Is this a long-term project (months to years) or something that will be used for a short period (weeks to a few months)?
  5. 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.

Thanks for your reply!

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)

@BeyK40 

 

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:

flexyourdata_0-1652901792082.png

 

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.

 taskgroupfilter.gif

It worked, thanks a lot!