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

[thp510]

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.

	A	B	C
1	Account	Owner	Segment
2	Tesla	Jess	COM
3	Brookstone	Kelly	SMB
4	GoPro	Jess	SMB
5	Apple	Linda	ENT
6	Google	Kelly	ENT
7	Netflix	Jess	COM

<tbody>
</tbody>

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...

	H	I	J
1	Pick Rep:	Jess
2
3	Account	Segment
4	Tesla	COM
5	GoPro	SMB
6	NetFlix	COM
7

<tbody>
</tbody>

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!

 

[Peter_SSs]

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

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Account	Owner	Segment					Rep	Jess	3
2	Tesla	Jess	COM
3	Brookstone	Kelly	SMB					Account	Segment
4	GoPro	Jess	SMB					Tesla	COM
5	Apple	Linda	ENT					GoPro	SMB
6	Google	Kelly	ENT					Netflix	COM
7	Netflix	Jess	COM
8
Multiple Rows

 

[thp510]

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

	A	B	C	D	E	F	G	H	I	J
1	Account	Owner	Segment					Rep	Jess	3
2	Tesla	Jess	COM
3	Brookstone	Kelly	SMB					Account	Segment
4	GoPro	Jess	SMB					Tesla	COM
5	Apple	Linda	ENT					GoPro	SMB
6	Google	Kelly	ENT					Netflix	COM
7	Netflix	Jess	COM
8

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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

 

[thp510]

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?

=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))))

 

[thp510]

Ahh, it looks like I was referencing your J3 in my code when I should have referenced your I3. It worked!!!

 

[Peter_SSs]

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.

 

[Fazza]

For info: in Excel at least, a paramterised query is great for this sort of thing. No code, no formulas.

Google will find examples, including this old one http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

 

[thp510]

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.

 

[Peter_SSs]

Appreciate troubleshooting this. Super helpful.

No problem.