Display different rows for different users

[Lesjoan01]

Hi,
I have a master spreadsheet that contains information provided by various users. At the moment each user sends an update and then this is manually copied into the master spreadsheet.

My question/query is:
Can I set up my spreadsheet so that when the user opens it they only see the cells/information that are relevant to them. They then update the risks that are assigned to them and send it back to me, this in turn updates the master spreadsheet.

I have seen articles that suggest using VBA or Power Query but I do not know where to start.

Example spreadsheet (master)

Risk	Update	Owner
Risk1	Update 1	Owner 1
Risk2	Update 2	Owner 1
Risk3	Update 3	Owner 2
Risk4	Update 4	Owner 2
Risk5	Udpate 5	Owner 3
Risk6	Update 6	Owner 3
Risk7	Update 7	Owner 4
Risk8	Udpate 8	Owner 4
Risk9	Update 9	Owner 5
Risk10	Update 10	Owner 5

Example spreadsheet (what Owner 1 would see)

Risk	Update	Owner
Risk1	Update 1	Owner 1
Risk2	Update 2	Owner 1

Thanks in anticipation of one of the many geniuses on here knowing the answer

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). The newer version of Excel have some cool new functions that may simplify tasks like these.

Also, what exactly is in the "Owner" field?
Is it the network ID for each user?
If not, how is Excel supposed to know which user is opening the file, and how to tie that back to the values in the Owner column?

 

[Lesjoan01]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). The newer version of Excel have some cool new functions that may simplify tasks like these.

Also, what exactly is in the "Owner" field?
Is it the network ID for each user?
If not, how is Excel supposed to know which user is opening the file, and how to tie that back to the values in the Owner column?

Hi Joe4,

thanks for the tip re updating my details - I have done this.

We are using 365 on Windows.

The 'Owner' field would be a specific users name - at the moment it is just their name so may not necessarily be the actual network name - however, that could be changed so that the Excel knows who is opening the file.

Many thanks
Lesley

 

[Joe4]

One way is to do something like the following:

1. Set up filters on this data range/table.
2. Then put the following VBA code in the "ThisWorkbook" module of the VB Editor:

Private Sub Workbook_Open()

    Dim rng As Range
    Dim owner As String

'   Specify sheet to apply this to
    Sheets("Sheet2").Activate
    
'   Assuming data starts in cell A1, set range
    Set rng = Range("A1").CurrentRegion
    
'   Prompt user for owner name
    owner = InputBox("Please enter your name")
    
'   Remove any old filers
    ActiveSheet.Range("$A$1:$C$11").AutoFilter Field:=3
    
'   Apply new filter
    ActiveSheet.Range("$A$1:$C$11").AutoFilter Field:=3, Criteria1:=owner _
        , Operator:=xlAnd
        
End Sub

*Note that you will need to update this line:

    Sheets("Sheet2").Activate

to match the name of the sheet where this data resides.

Save the file and close it.

Now, whenever you open the file, it will prompt the user for their name and then apply that to the Owner filter in the third column.
Obviously, you can modify this, but it should at least give you a good point to start from.

 

[Lesjoan01]

One way is to do something like the following:

1. Set up filters on this data range/table.
2. Then put the following VBA code in the "ThisWorkbook" module of the VB Editor:

Private Sub Workbook_Open()

    Dim rng As Range
    Dim owner As String

'   Specify sheet to apply this to
    Sheets("Sheet2").Activate
   
'   Assuming data starts in cell A1, set range
    Set rng = Range("A1").CurrentRegion
   
'   Prompt user for owner name
    owner = InputBox("Please enter your name")
   
'   Remove any old filers
    ActiveSheet.Range("$A$1:$C$11").AutoFilter Field:=3
   
'   Apply new filter
    ActiveSheet.Range("$A$1:$C$11").AutoFilter Field:=3, Criteria1:=owner _
        , Operator:=xlAnd
       
End Sub

*Note that you will need to update this line:

    Sheets("Sheet2").Activate

to match the name of the sheet where this data resides.

Save the file and close it.

Now, whenever you open the file, it will prompt the user for their name and then apply that to the Owner filter in the third column.
Obviously, you can modify this, but it should at least give you a good point to start from.

Hi Joe,
thank you so much for this. I will give that a go and let you know how I get on. Appreciate your assistance with this.

 

[Joe4]

You are welcome.

 

[Lesjoan01]

You are welcome.

Hi Joe4,

just wanted to let you know that it worked (not that I had any doubts, more doubts around my ability to get it working!!!).

Again, thanks very much for your help with this.

Take care
Lesley

 

[Joe4]

Hi Joe4,

just wanted to let you know that it worked (not that I had any doubts, more doubts around my ability to get it working!!!).

Again, thanks very much for your help with this.

Take care
Lesley

You are welcome.
Glad I was able to help!