Drop down list selection to fill down results on multiple rows

SanctiMortem

New Member
Joined
Sep 29, 2016
Messages
4
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:
1634262919349.png


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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,233
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Sep 29, 2016
Messages
4
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:

1634568115805.png



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

Well-known Member
Joined
Jul 5, 2014
Messages
18,233
Office Version
  1. 2013
Platform
  1. Windows
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")

VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,739
Members
425,429
Latest member
MMMMMM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top