Select only those rows from a table matching the user who opens the Spreadsheet

[Frank Bacchus]

So, this should be fun. I have a spreadsheet with many rows and columns. For my question, I have simplified it to two columns and several rows (see attachment). One column contains the "Assigned to" person id and the next column contains an amount. The spreadsheet resides on SharePoint, so only one person can check it out at any given time.

I would like to display only those records the belongs '(assigned to)' the person opening the sheet. Thanks this example below:

Assigned to Amount
Frank B 123
Mary S 345
Nancy R 468
Frank B 897
Amy S 234
Frank B 876

If frank B opens the spreadsheet (in edit mode), I would only like to see:
Assigned to Amount
Frank B 123
Frank B 897
Frank B 876

Thus, I believe capturing the name and hiding the rows that do not apply. How can this be done?

frank

 

[David Evans]

Good Morning Frank -

Here's a quick and dirty way of doing it - I think you can improve on it with a bit more time ...
I made your Table into an Excel Table - named Table1. I use a UDF to get the username and place it into B2. Run the Macro Filter_Table.

When you open the file, it will throw your username into B2 - edit the Assign to to reflect this name and try running the macro - should work - it does on my PC

 

[neeraj2050]

Excellent!!

May I request you to extend your excellence into below mentioned query

1)How to get the user name automatically
2) How to get the sheet name onto the cell automatically

 

[David Evans]

Excellent!!

May I request you to extend your excellence into below mentioned query

1)How to get the user name automatically
2) How to get the sheet name onto the cell automatically

The user name is "automatically" inserted into B1 when the file is opened. The UDF (User Defined Function) that does this can be found in Module 1. The code is very simple

Function username()
username = Environ("username")
End Function

Insofar as the sheetname is concerned, I've used a formula to get the sheetname in B3. It can also be done with VBA, depending on your preferences - see the latest version of the file below

 

[Frank Bacchus]

The user name is "automatically" inserted into B1 when the file is opened. The UDF (User Defined Function) that does this can be found in Module 1. The code is very simple

Function username()
username = Environ("username")
End Function

Insofar as the sheetname is concerned, I've used a formula to get the sheetname in B3. It can also be done with VBA, depending on your preferences - see the latest version of the file below

Thanks a lot David!

 

[neeraj2050]

Thanks a lot!!

May I know where to put this code into excel as I am begiiner into VBA

I am getting message Run time error 1004 No data was selected to Parse

 

[David Evans]

Thanks a lot!!

May I know where to put this code into excel as I am begiiner into VBA

I am getting message Run time error 1004 No data was selected to Parse

Open up the developer Tab, Insert a Module and paste the code in there ...it should work fine. If not, you might want to read through the notes on VBA on the Forum ...