Hiding rows and columns from individual users in a shared table

Occasional Visitor

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.

3 Replies

Hi @Niktia 

I don't know if this will be the best way for you but it may be one way;

First, getting the current user can be done using an old version of macros by defining it as a name.

=GET.WORKBOOK(35)

bosinander_0-1642662080932.png

...and in a cell the formula =windowsUser

The workbook will have to be saved as an xlsm since it holds active content, fetching data from outside excel. 

 

@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

bosinander_0-1642663088096.png

User Bravo

bosinander_2-1642663323606.png

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.

 

@Niktia 

If IMPORTRANGE() we speak about Google Sheets, correct? I'm not familiar with it, but does it ignore hided columns?