Need some help on filters


#1

I have a login username spreadsheet for my company. Certain of the columns will be filtered to populate spreadsheets elsewhere (for example, spreadsheets for specific staff listing their usernames would only return columns for name, web link, username and how to reset password). Column A is Login Name, B is Website, C is how to obtain credentials, D is how to reset password and then Columns E onward are employee names.

I’m trying to filter this username sheet to another spreadsheet, so the data is specific to an employee. So say I have Person A, B and C. They have a login for Portals A, B and/or C. I’d like set up a login spreadsheet for person B that lists all of the logins and their usernames. It shouldn’t include the usernames of other employees.

I think the filter to do the following:

First, find the column that says “Person B”, and then return those rows where that column with “Person B” as the header is populated with a username (i.e. not null). But also only return Columns A, B, D and the username column for “Person B”. In other words, find Person B’s username column, return each row with usernames entered but only return column A (name of login), column B (website location), column D (how to reset password) and the username column.

How do I do this? Please help! Thanks!


#2

Hi @bfarnan,

How about something like this:


#3

That isn’t quite what i’m looking for. Can you make that spreadsheet you linked to be editable? I operate in a HIPAA secure environment, so sharing google sheets outside our organization is difficult/impossible. Working on yours would be eaiser…


#4

Yes, I have given you access. Thanks for your edits!


#5

Thanks–I made the changes.

So i’d like to make a username list for Jon.

It would search row 1 for Jon Smith (which is column E), then pull every row where this column E is populated (not null), which in this case is Portal A and C. And only return Column E (with the usernames) and A, B and D.


#6

@bfarnan sorry can’t follow. Could you plot your expected output and I will take it from there?

Thanks!

image


#7

Done. let me know if you have any other questions. I really appreciate your help!


#8

@bfarnan I think this is it. :slight_smile:


#9

Thanks. That is where I got as well. But it is one step away. What I was hoping for was a way to set the filter so we don’t identify the specific username column by it’s letter, but instead by the name of the staff member.

Reason this is a problem: If Jon Smith data was in Column G, her sheetgo coding would require that her data remain in column G. If a column was deleted that changed her column (say an employee leaves), then Jon 's column has moved–say to Column F. Now his sheetgo coding is still pulling data from Column G, which is data from another staff member’s column. I’d rather the filter look for the staff member name and know to pull data from Column G.

The only workaround I have now is to keep the columns consistent and blank out the data viewable by staff if the columns return data for a different staff member. That would queue a complaint that would require that I fix the sheetgo coding. Obviously a less ideal workflow than the filter doing the work for me.


#10

Seems to me a solution would be to add one column with the particular id for that person:

image