Forum Discussion

XesppX's avatar
XesppX
Copper Contributor
Jan 12, 2024

Automatically hide columns without data in a large document

Hi,

 

I’m actually wondering if this is possible to achieve, and how advanced it is if so?

I work in a large RACI matrix, and it is becoming quite clear that there are quite a few roles and activities.

 

In short, the columns consist of roles (CEO, COO, HR, financial manager, etc., etc.), and the rows consist of activities (Board meetings, equipment procurement, setting up IT systems, etc., etc.).

I now see that the document we are working on has around 50 roles and nearly 150 activities. When you then start to fill in responsibility for all different roles/activies, it becomes quite difficult to navigate the matrix. Just a picture to show how it can look, ours is way bigger:

 

Is it possible that if I click on activity 1: only the colums on that row with data on the will be displayed? I can explain using the picture above. What I want excel to do: If I click on "Budget" (A3) in the matrix, I want it to hide B3, C3, F3 since they don't have any data, and only show D3, F3, G3 and H3.  I want it to do this for all the activies in the A-column. Is there any feature that can do this, and if so, is this hard to achieve? 


Best regards, 

 

 

Excel
Formulas and Functions
Macros and VBA
training
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 12, 2024

    XesppX 

    I suspect you are hoping for a filter button that magically conceals the columns that are not relevant to a specific task.  This approach uses 365 formulas and is somewhat different.

    The formula is

    = LET(
    selection, XLOOKUP(selectedActivity, Activity, assignments),
    TRANSPOSE(FILTER(VSTACK(roles, selection), selection<>"", "None"))
  )

    and it returns a column of data for the selected activity

Resources