Create data validation list without blank with sources in different columns

aishnadar · Nov 22, 2016

I have table containing questions and multiple responses for each question (table format as below). I want to convert these responses as drop down list (without blanks) for each question. Data validation list is giving me drop down with blanks. How do I remove blanks. Since I have 50 questions, manually defining names with offset is too tedious. Is there any other way?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Questions[/TD]
[TD]Res 1[/TD]
[TD]Res 2[/TD]
[TD]Res 3[/TD]
[TD]Res 4[/TD]
[/TR]
[TR]
[TD]Qs 1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Qs 2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Pls help

Eric W · Nov 22, 2016

Consider this layout:

	A	B	C	D	E	G
Questions	Response 1	Response 2	Response 3	Response 4	How many?	List
How many?	A lot	None	A lot
When?	Never	1st of June	None
What time?	Noon
Pet's name?	Spot	Fido	Jinx
Favorite State?	Maine	Florida	Iowa

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$2:$E$6,MATCH($G$1,$A$2:$A$6,0),SMALL(IF(($A$2:$A$6=$G$1)*($B$2:$E$6<>""),COLUMN($B$1:$E$1)-COLUMN($B$1)+1),ROWS($J$2:$J2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Put a Data Validation rule in G1, where Allow: is List, and the Source: is =A2:A6 (your list of questions).

Put the formula in J2, change the ranges to match your sheet, then confirm it with Control+Shift+Enter. Then copy it down the column for as many rows as you have questions. This list generates a list of answers to the question selected in G1, without spaces.

Now put a data validation in H1, where Allow: is List, and the Source: is =OFFSET($J$2,0,0,COUNTA($J$2:$J$5))

This points to the list generated in G.

Let us know if this helps.

aishnadar · Nov 28, 2016

Thanks for the response Eric.

Actually, my request is a bit more complicated.

I have set of questions that are randomly picked up from a big list of questions. So the data validation list is dependent on each of these questions to provide response options

So from your table (let' call it "guide" table), the list of questions with their corresponding response options (multiple choice) are provided.
In another sheet ("MyQs" tab), From A2 to A10, I use random formula to generate my question list. Depending on the Question picked, B2 to B10 should provide drop down list using Data validation.

Sorry for the confusion.

Eric W said:
Consider this layout:

A B C D E F G H I J
Questions Response 1 Response 2 Response 3 Response 4 How many? List
How many? A lot None A lot
When? Never 1st of June None
What time? Noon
Pet's name? Spot Fido Jinx
Favorite State? Maine Florida Iowa

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$2:$E$6,MATCH($G$1,$A$2:$A$6,0),SMALL(IF(($A$2:$A$6=$G$1)*($B$2:$E$6<>""),COLUMN($B$1:$E$1)-COLUMN($B$1)+1),ROWS($J$2:$J2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Put a Data Validation rule in G1, where Allow: is List, and the Source: is =A2:A6 (your list of questions).

Put the formula in J2, change the ranges to match your sheet, then confirm it with Control+Shift+Enter. Then copy it down the column for as many rows as you have questions. This list generates a list of answers to the question selected in G1, without spaces.

Now put a data validation in H1, where Allow: is List, and the Source: is =OFFSET($J$2,0,0,COUNTA($J$2:$J$5))

This points to the list generated in G.

Let us know if this helps.

aishnadar · Nov 28, 2016

So I used the below formula in Data validation. But, I get blank cells as well listed.

=INDIRECT("Guide!$K"&MATCH($A3,Guide!$F:$F,0)&":$U"&MATCH($A3,Guideline!$F:$F,0))

How do I get rid off the blank cells?

Eric W · Nov 28, 2016

You can use the same type of formula to get rid of the blank cells. But you'd need 1 formula for each question, so 9 formulas on the Guide sheet, and then each of the Data Validation on the MyQs sheet points to a different column. You may want to consider VBA to set things up a bit easier.

A few questions about your layout. Is the Guide sheet set up like my example? i.e., questions running down column A, 4 possible answers in B:E? When you say the questions are selected randomly, is that with a formula, or with VBA? If it's with a formula, they will all change when you enter an answer, right? Let me know how that works, and I'll give you some options.

aishnadar · Nov 29, 2016

Eric W said:
You can use the same type of formula to get rid of the blank cells. But you'd need 1 formula for each question, so 9 formulas on the Guide sheet, and then each of the Data Validation on the MyQs sheet points to a different column. You may want to consider VBA to set things up a bit easier.

A few questions about your layout. Is the Guide sheet set up like my example? i.e., questions running down column A, 4 possible answers in B:E? When you say the questions are selected randomly, is that with a formula, or with VBA? If it's with a formula, they will all change when you enter an answer, right? Let me know how that works, and I'll give you some options.

My objective is to finally create this in VBA.
But this is an experimental tool that I'm creating as prototype.

Basically, my sheet starts with some basic questions. Based on the responses, the follow up questions are generated.
My mistake, its not randomly generated, but the question list is generated depending on the responses the user would give.

Relally appreciate your help

deciog · Nov 29, 2016

aishnadar

My contribution

According to Post # 2 Now place a data validation on H1 using this formula

=OFFSET($K$2,0,0,COUNTIF($K$2:$K$6,">"""))

I hope I have helped

Decio

Eric W · Nov 29, 2016

It appears that you've got a fairly significant project. It's hard to see how I can help here, since this site is dedicated more to answering questions about pieces of a puzzle, not designing the puzzle. You may want to get a consultant.

Nevertheless, I can suggest how to do part of it. Let's say your Guide sheet looks like this:

	A	B	C	D	E
Questions	Response 1	Response 2	Response 3	Response 4	Select
How many?	A lot	None
When?	Never	1st of June
What time?	Noon
Pet's name?	Spot	Fido	Jinx
Favorite State?	Maine	Florida	Iowa
Zodiac sign?	Aries	Cancer	Leo	Pisces
Age?
Height?	5"	6"
Month?	June	December
Color?	Red	Blue	Green
Class?	Algebra	English	History

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"][/TD]

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

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

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

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

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

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

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

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

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

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Guide

Your questions are in column B, then answers in C:F. In column N is the list of questions you want to put on the MyQs sheet. You'd select them according to your initial questions. These could be asked from a UserForm, kicked off by a Worksheet_Open event perhaps. That's another piece.

Now right click on the sheet tab on the bottom and select View Code. From the menu, click Insert > Module. Then paste this code:

Rich (BB code):

Sub MakeQs()
Dim SrcSht As Worksheet, TrgSht As Worksheet
Dim r As Long, c As Range, str1 As String, x As String, mc As Long

    Set SrcSht = Sheets("Guide")
    Set TrgSht = Sheets("MyQs")
    
    TrgSht.Cells.ClearContents
    r = 2
    For Each c In SrcSht.Range("N2:N10")
        If c.Value <> "" Then
            TrgSht.Cells(r, "A") = SrcSht.Cells(c.Value + 1, "B")
            str1 = ""
            For mc = 3 To 6
                x = SrcSht.Cells(c.Value + 1, mc)
                If x <> "" Then
                    str1 = str1 & x & ", "
                End If
            Next mc
            If str1 <> "" Then str1 = Left(str1, Len(str1) - 2)
            With TrgSht.Cells(r, "B").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:=str1
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
            r = r + 1
        End If
    Next c
                
End Sub

Change the sheet names in red to match your sheet, and the sheets must exist. Now close the VBA editor, and in the Excel window press Alt-F8, select the MakeQs macro and click Run. The questions selected from the Guide sheet will be copied to the MyQs sheet, and a Data Validation (without spaces!) will be copied to the cell next to the question.

Hopefully this gives you some direction.

Create data validation list without blank with sources in different columns

aishnadar

New Member

Excel Facts

Eric W

MrExcel MVP

aishnadar

New Member

aishnadar

New Member

Eric W

MrExcel MVP

aishnadar

New Member

deciog

Board Regular

Eric W

MrExcel MVP

Similar threads

Forum statistics

Share this page

Create data validation list without blank with sources in different columns

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock