Drop down list selection to fill down results on multiple rows

[SanctiMortem]

Hi!

I've been breaking my head trying to figure out and or find an answer on how to use a drop down list (of names) to fill down a column with the results.

More detail.

Lets say I have multiple sheets, each with a name, an a list of objects they own like this:

And on another sheet, I have a drop down list of those names, and when I select one of those names it will populate the column with the list of items that name has.

each person doesn't have to be on different sheets.. but I don't know how to keep it organized any other way. The list will keep growing as time passes and I need a way to consult the data summarized since the main sheet of each person has dates on when they acquired such item, how long has it been since last used if used at all, and if they want to sell it or not.
So I figured its easier to look up for a name and get a list of all the items they own, and when the got each item.


Please help I am desperate to find a solution.. I'm open to other ways too, I just need a way to simplify it, as you can imagine the list grows a lot over time, and a single table horizontally won't work, because the amount of items each person has will make it impossible to read.

Help
and thanks in advance!

Best Regards.

 

[My Aswer Is This]

So in your example. You have John in Range("B2")
So will the sheet this is on be named "John"

And the name will always be in Range("B2") of every sheet and the list of objects will always start in Range("C3") is this true. If not explain more.

And you said:
has dates on when they acquired such item, how long has it been since last used if used at all, and if they want to sell it or not.
Where will this data be on each sheet?

And what is the name of the sheet where you plan to enter the name and then expect to see the results

See when you say something like below it needs to be specific like sheet named "Alpa"
You said:
And on another sheet, I have a drop down list of those names,

 

[SanctiMortem]

Hi! thank you for the response, and sorry for the delay on my part.

So my idea was to indeed have a sheet for each person. In that case the sheet would be indeed named after each person (in the example it would be John) But not necessarily has to be like that, I just don't know of a better option.

So regarding you questions:
- So will the sheet this is on be named "John"? - Yes, in this example it will

- And the name will always be in Range("B2") of every sheet and the list of objects will always start in Range("C3") is this true. If not explain more. - Well, again, that was just an example, I usually skip one line to do the tables and such... But if it helps, "yes" all of the sheets will have it start on the same cell, all cells will start exactly on the same cells always.

- Where will this data be on each sheet? - I's sorry I didn't post an example of that.. The data will be on the table of each person, like this:

And what is the name of the sheet where you plan to enter the name and then expect to see the results - It will be on the first Sheet (Or maybe another Excel file not sure what is better) and the name will be "Consult".


See when you say something like below it needs to be specific like sheet named "Alpa"
You said:
And on another sheet, I have a drop down list of those names, - Yes, I know, I'm so sorry about it. In my head it was perfectly clear lol, but I know I sometimes forget to actually write it down.

The another sheet will be "Consult" the one where you will have a cell with a drop down menu (or this is how I believe I could do it) where you will have the names of all the users, once you select a name I want to pull or call out the data of their sheet.

Now the idea is for this to make it easier to consult each person instead of going through the different sheets looking for the person (there will be more than 50+. I though of doing all of the data in the same table, but I don't know how to administrate the table so that it will be readable when adding all the data. Maybe having the names also as a drop down menu and just having all of the items added in the same table maybe?


Thank you very much!
Best Regards!

 

[My Aswer Is This]

OK here is my script:
On sheet named Consult you select a sheet name in Range("B2")
So if you choose "Alpha the script will look in sheet named "Alpha" and copy all the data from sheet named "Alpha" Range("C5:F" &lastrow)

I believe this is what you want.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Put this script in sheet named "Consult"

When you select a sheet name in Range("B2") of sheet named "Consult" the script will run
I suggest you use a Data Validation list in Range("B2") so all you do is select the sheet name in Range ("B2")

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/18/2021  9:41:38 PM  EDT
    On Error GoTo M
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Address = "$B$2" Then
        Cancel = True
        Dim Lastrow As Long
        Lastrow = Sheets(Target.Value).Cells(Rows.Count, "C").End(xlUp).Row
        Sheets("Consult").Columns(3).Resize(, 3).Clear
        Sheets(Target.Value).Cells(3, 3).Resize(Lastrow, 4).Copy Sheets("Consult").Cells(4, 3)
End If
Exit Sub
M:
MsgBox "You selected a sheet name that does not exist" & vbNewLine & "You selected  " & Target.Value

End Sub

 

[SanctiMortem]

Hi!

Thank you so much! I will try it out now! thank you!