data validation help

G

graham_tyrell

Board Regular
Joined
Jun 9, 2013
Messages
50
Hello All

Can the following be done in Excel.I have 10 groups with 8 people in each group.Using a data validation list can it display all the 10 groups (group 1 ,group 2 ,group 3 etc etc.. down to group 10) in a dropdown box so when a group is selected it shows all the 8 names in a merged cell (8 cells merged)

Is this only possible with VBA.Is formulas possible

Regards

Graham
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Enter the groups (say, g1, g2, etc.) in a range (in my test I had 2 groups in N4:N5 and each group had 2 people).

In the adjacent column enter the names of the people using ALT+enter to separate them. So, if the 2 names of the people in G1 are A and B, type A ALT+ENTER B. So, O4 should have
A
B
and O5 should have
C
D.

Now, wherever you want the data validation (I picked N7) specify the valid list as your G1... range (N4:N5 in my test).

Then, in the adjacent cell enter the formula =INDEX(O4:O5,MATCH(N7,N4:N5,0)) Format this cell the same as O4 and you will get the result.


graham_tyrell said:
Hello All

Can the following be done in Excel.I have 10 groups with 8 people in each group.Using a data validation list can it display all the 10 groups (group 1 ,group 2 ,group 3 etc etc.. down to group 10) in a dropdown box so when a group is selected it shows all the 8 names in a merged cell (8 cells merged)

Is this only possible with VBA.Is formulas possible

Regards

Graham
Click to expand...
 
Upvote 0
The cells to show names are not merged to allow the names to appear below each other.

The Data validations E2 and results in G2:G9.

Marty


*
A
B
C
D
E
F
G
*
Group ID
*
Data Select
*
Result
Group 1
Group 1
*
Group 1
*
a
Group 2
Group 1
*
*
*
b
Group 3
Group 1
*
*
*
c
Group 4
Group 1
*
*
*
d
Group 5
Group 1
*
*
*
e
Group 6
Group 1
*
*
*
f
Group 7
Group 1
*
*
*
g
Group 8
Group 1
*
*
*
h
Group 9
Group 2
*
*
*
*
Group 10
Group 2
*
*
*
*
*
Group 2
*
*
*
*
*
Group 2
*
*
*
*
*
Group 2
*
*
*
*
*
Group 2
*
*
*
*
*
Group 2
*
*
*
*
*
Group 2
*
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="align: left"]Name
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="align: left"]a
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="align: left"]b
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="align: left"]c
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="align: left"]d
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]

[TD="align: left"]e
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]

[TD="align: left"]f
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]

[TD="align: left"]g
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]

[TD="align: left"]h
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]

[TD="align: left"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]11
[/TD]

[TD="align: left"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]12
[/TD]

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

[TD="bgcolor: #CACACA, align: center"]13
[/TD]

[TD="align: left"]4
[/TD]

[TD="bgcolor: #CACACA, align: center"]14
[/TD]

[TD="align: left"]5
[/TD]

[TD="bgcolor: #CACACA, align: center"]15
[/TD]

[TD="align: left"]6
[/TD]

[TD="bgcolor: #CACACA, align: center"]16
[/TD]

[TD="align: left"]7
[/TD]

[TD="bgcolor: #CACACA, align: center"]17
[/TD]

[TD="align: left"]8
[/TD]

</tbody>

Spreadsheet Formulas

Cell
Formula
G2
{=IF(ROWS(G2:G$2)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G2:G$2))-ROW($B$2)+1),"")}
G3
{=IF(ROWS(G$2:G3)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G3))-ROW($B$2)+1),"")}
G4
{=IF(ROWS(G$2:G4)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G4))-ROW($B$2)+1),"")}
G5
{=IF(ROWS(G$2:G5)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G5))-ROW($B$2)+1),"")}
G6
{=IF(ROWS(G$2:G6)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G6))-ROW($B$2)+1),"")}
G7
{=IF(ROWS(G$2:G7)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G7))-ROW($B$2)+1),"")}
G8
{=IF(ROWS(G$2:G8)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G8))-ROW($B$2)+1),"")}
G9
{=IF(ROWS(G$2:G9)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G9))-ROW($B$2)+1),"")}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Sheet2

*ABCDEFG
*Group ID*Data Select*Result
Group 1Group 1*Group 1*a
Group 2Group 1***b
Group 3Group 1***c
Group 4Group 1***d
Group 5Group 1***e
Group 6Group 1***f
Group 7Group 1***g
Group 8Group 1***h
Group 9Group 2****
Group 10Group 2****
*Group 2****
*Group 2****
*Group 2****
*Group 2****
*Group 2****

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 74.4px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 73.6px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: left"]Name[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: left"]a[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: left"]b[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: left"]c[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: left"]d [/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: left"]e[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: left"]f[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: left"]g [/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: left"]h[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: left"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: left"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

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

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: left"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="align: left"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="align: left"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]

[TD="align: left"]7[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
G2{=IF(ROWS(G2:G$2)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G2:G$2))-ROW($B$2)+1),"")}
G3{=IF(ROWS(G$2:G3)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G3))-ROW($B$2)+1),"")}
G4{=IF(ROWS(G$2:G4)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G4))-ROW($B$2)+1),"")}
G5{=IF(ROWS(G$2:G5)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G5))-ROW($B$2)+1),"")}
G6{=IF(ROWS(G$2:G6)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G6))-ROW($B$2)+1),"")}
G7{=IF(ROWS(G$2:G7)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G7))-ROW($B$2)+1),"")}
G8{=IF(ROWS(G$2:G8)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G8))-ROW($B$2)+1),"")}
G9{=IF(ROWS(G$2:G9)<=COUNTIF($C$2:$C$80,$E$2),INDEX($B$2:$B$80,SMALL(IF($C$2:$C$80=$E$2,ROW($C$2:$C$80)),ROWS(G$2:G9))-ROW($B$2)+1),"")}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Data Validation in Spreadsheet
CellAllowDatasInput 1Input 2
E2List*=$A$2:$A$11*

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Not sure how to interpret the comment. ;-)

Do you want them to go across or down? If the latter see my comment about copying the format.

graham_tyrell said:
Thanks Tushar

Works great except the names are going across the cell instead of down the cell

Thanks again
Click to expand...
 
Upvote 0
Thanks

I have the following....in D3 & D4 I used A ALT+ENTER B , C ALT + ENTER D
D3 has
A
B
D4 has
C
D.
Data Validation in A5 and formula in B5 =INDEX(D3:D4,MATCH(A5,C3:C4,0))
C3 - Group1
C4 - Group2
When I select A5 (DV) and group1 the cell B5 displays A B (across the cell) instead of
A
B
I need it to display down under each other instead of across the cell

I must be missing something

Thanks
 
Last edited:
Upvote 0
Format the cell so that 'Wrap Text' is checked (right-click the cell | Format Cells... | Alignment tab)
graham_tyrell said:
Thanks

I have the following....in D3 & D4 I used A ALT+ENTER B , C ALT + ENTER D
D3 has
A
B
D4 has
C
D.
Data Validation in A5 and formula in B5 =INDEX(D3:D4,MATCH(A5,C3:C4,0))
C3 - Group1
C4 - Group2
When I select A5 (DV) and group1 the cell B5 displays A B (across the cell) instead of
A
B
I need it to display down under each other instead of across the cell

I must be missing something

Thanks
Click to expand...
 
Upvote 0
When using the ALT+ENTER it works good except for when I remove a name.The cell still remains the same size...When removing names or adding names is there a way to adjust the size of the cell...thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
380
RoryA
RoryA
P
  • Question Question
Lambda or VBA to solve the pairs webinar problem!!!
Replies
0
Views
101
Phantom1
P
D
  • Solved
Custom UI Tab Not Working
Replies
7
Views
845
Dman333
D
L
  • Question Question
Searchable data validation list problem
Replies
0
Views
51
Lschuman
L
T
  • Question Question
Grouping rows by using data validation
Replies
1
Views
223
Joe4
Joe4

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

Share this page

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