Hiding rows and columns from individual users in a shared table

%3CLINGO-SUB%20id%3D%22lingo-sub-3065860%22%20slang%3D%22en-US%22%3EHiding%20rows%20and%20columns%20from%20individual%20users%20in%20a%20shared%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3065860%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20somehow%20hide%20the%20columns%20in%20the%20general%20table%20for%20specific%20users%3F%20With%20the%20%22IMPORTRANGE%22%20formula%2C%20you%20have%20to%20create%20many%20tables%2C%20which%20is%20inconvenient.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3065860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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?