Forum Discussion

Niktia's avatar
Niktia
Copper Contributor
Jan 19, 2022

Hiding rows and columns from individual users in a shared table

Good day!    Can I somehow hide the columns in the general table for specific users? With the "IMPORTRANGE" formula, you have to create many tables, which is inconvenient.
excel
Formulas and Functions
bosinander's avatar
bosinander
Steel Contributor
Jan 19, 2022

Niktia, Hi.

 

This is a way to chose columns where you define each users selection in A2:E3.

Select a User in J2

J5 will spill to show the table but only the defined columns.

 

User Alfa

User Bravo

It is all done in one cell, J5;

=LET(
  columnSelection;        XLOOKUP(J2;A:A;B:E);
  colSelPossibleToFilter; TRANSPOSE(columnSelection);
  colNumsWithoutZeros;    FILTER(colSelPossibleToFilter;colSelPossibleToFilter<>0);
  coINums;                TRANSPOSE(colNumsWithoutZeros);
  rowNums;                SEQUENCE(ROWS(Table1[#All]));
  output;                 INDEX(Table1[#All];rowNums;coINums);
  output
)

Delimiting rows can be done by appending FILTER() to the output.