Help getting multiple rows of data from a selected drop down list...

thp510

Board Regular
Joined
Oct 19, 2015
Messages
110
Office Version
  1. 365
Platform
  1. Windows
I have the following rows of data in my sheet. Here's 7 as an example, but I essentially have over 1000 rows of data.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Account
[/TD]
[TD]Owner
[/TD]
[TD]Segment
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Tesla
[/TD]
[TD]Jess
[/TD]
[TD]COM
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Brookstone
[/TD]
[TD]Kelly
[/TD]
[TD]SMB
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]GoPro
[/TD]
[TD]Jess
[/TD]
[TD]SMB
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Apple
[/TD]
[TD]Linda
[/TD]
[TD]ENT
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Google
[/TD]
[TD]Kelly
[/TD]
[TD]ENT
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Netflix
[/TD]
[TD]Jess
[/TD]
[TD]COM
[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is have a drop down list or a pick list setup so that when I select Jess from the picklist, all of Accounts show up like this...

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD="align: right"]Pick Rep:
[/TD]
[TD="align: center"]Jess
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="align: center"]Account
[/TD]
[TD="align: center"]Segment
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="align: center"]Tesla
[/TD]
[TD="align: center"]COM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: center"]GoPro
[/TD]
[TD="align: center"]SMB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="align: center"]NetFlix
[/TD]
[TD="align: center"]COM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What excel formula do I put in cell H4 (and the rest of the rows in that column downwards) so that every time I change the account owner in cell I1, the appropriate list of accounts show up? If this isn't possible what other options do I have? I figured out the equation for I4 downwards =INDEX(A:A, MATCH(H4, C:C, 0)) but just need cells from H4 down to H1000.

Thanks!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try this. Formulas in H4:I4 copied down as far as you might ever need.

Excel Workbook
ABCDEFGHIJ
1AccountOwnerSegmentRepJess3
2TeslaJessCOM
3BrookstoneKellySMBAccountSegment
4GoProJessSMBTeslaCOM
5AppleLindaENTGoProSMB
6GoogleKellyENTNetflixCOM
7NetflixJessCOM
8
Multiple Rows
 
Upvote 0
Thank you Peter! However, is there another function I can use besides the AGGREGATE function? I'm trying to get this on Google sheets and it looks like they don't have it available.




Try this. Formulas in H4:I4 copied down as far as you might ever need.

Multiple Rows


ABCDEFGHIJ
AccountOwnerSegment



RepJess
TeslaJessCOM






BrookstoneKellySMB



AccountSegment
GoProJessSMB



TeslaCOM
AppleLindaENT



GoProSMB
GoogleKellyENT



NetflixCOM
NetflixJessCOM

















<colgroup><col style="font-weight:bold; width:30px; "><col style="width:85px;"><col style="width:56px;"><col style="width:71px;"><col style="width:17px;"><col style="width:17px;"><col style="width:17px;"><col style="width:17px;"><col style="width:85px;"><col style="width:84px;"><col style="width:26px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
J1=COUNTIF(B2:B2000,I1)
H4=IF(ROWS(H$4:H4)>J$1,"",INDEX(A$2:A$2000,AGGREGATE(15,6,(ROW(A$2:A$2000)-ROW(A$2)+1)/(B$2:B$2000=I$1),ROWS(H$4:H4))))
I4=IF(H4="","",INDEX(C$2:C$2000,AGGREGATE(15,6,(ROW(C$2:C$2000)-ROW(C$2)+1)/(B$2:B$2000=I$1),ROWS(H$4:H4))))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Actually, I just moved it to excel and here's the code I'm using (different columns, rows, and worksheets). However can you see why I'm getting an error? I'm following along but see that I'm getting a #NUM ! error when I try to mimic your code in cell H4. Is this a syntax error somewhere?

Code:
=IF(ROWS(B$5:B10001)>C$3, "", INDEX(RawData!B$7:B$10000, AGGREGATE(15,6, (ROW(RawData!B$7:B$10000)-ROW(RawData!B$7)+1)/(RawData!AO$7:AO$10000=B$3),ROWS(B$5:B10001))))
 
Last edited:
Upvote 0
Ahh, it looks like I was referencing your J3 in my code when I should have referenced your I3. It worked!!!
 
Upvote 0
Ahh, it looks like I was referencing your J3 in my code when I should have referenced your I3. It worked!!!
Glad you got it working. :)

In future, if you are looking for a solution for Google Sheets, you should state that clearly at the beginning. This is an Excel forum, not a general spreadsheets forum, and helpers tend not to be too happy if they put time into developing a working solution only to find it wasn't an Excel question after all. ;)
 
Upvote 0
Of course! Sorry about that. I will say that I'm moving my model to Excel so your work is definitely being used! Appreciate troubleshooting this. Super helpful.

Glad you got it working. :)

In future, if you are looking for a solution for Google Sheets, you should state that clearly at the beginning. This is an Excel forum, not a general spreadsheets forum, and helpers tend not to be too happy if they put time into developing a working solution only to find it wasn't an Excel question after all. ;)
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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
Back
Top