join four columns in one array

rrocco

New Member
Joined
Sep 5, 2005
Messages
13
Hello,

I have four columns with data and i'd like to use this four columns in
my Data > Validation > List.
Unhappyly, Excel don't let use more than one column in List >
Validation. It shows me the the message the List source must be a
reference to a single column. So how can i figure it out without VBA ?


I was thinking in built a rangename that join this four column in 1
continuous array.


Thanks in advance
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
Hi,

Is Vba necessary?

<table border=1><tr><td align=left valign=center><font size=1 color=red face=verdana>Gönderen XL Ver.:</font><font size=1 face=verdana> Office 2003 / </font><font size=1 color=red face=verdana>OS Ver.:</font><font size=1 face=verdana> Windows XP </font></td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>A</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>B</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>C</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>D</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>E</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>F</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>1</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>1</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>11</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>21</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>31</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A1&B1&C1&D1'>1112131 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>5152535</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>12</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>22</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>32</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A2&B2&C2&D2'>2122232 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>13</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>23</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>33</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A3&B3&C3&D3'>3132333 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>4</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>4</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>14</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>24</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>34</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A4&B4&C4&D4'>4142434 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>5</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>5</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>15</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>25</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>35</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A5&B5&C5&D5'>5152535 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>6</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>6</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>16</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>26</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>36</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A6&B6&C6&D6'>6162636 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>7</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>7</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>17</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>27</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>37</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A7&B7&C7&D7'>7172737 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>8</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>8</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>18</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>28</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>38</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A8&B8&C8&D8'>8182838 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>9</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>9</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>19</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>29</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>39</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A9&B9&C9&D9'>9192939 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>10</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>10</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>20</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>30</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>40</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=A10&B10&C10&D10'>10203040 (ƒx)</ACRONYM></font></td><td bgcolor=white></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana>XLtoHTML v1.1 / ExcelTürkiye - 2006©</font></td></tr></table>

On F1 cell:
Data | Validation | List:
Code:
=$E$1:$E$10

Ok?
 

rrocco

New Member
Joined
Sep 5, 2005
Messages
13
Hi Erdinç, thanks a lot for your reply.

Look, taking your example, i'd like that my validation accept the numbers: 1, 11, 21,31. By your soluction, the validation will accept only the number 1112131.

Cheers
 

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
I know that. Its concacenated cell values.

But i don't know some method to solve your request by using Validation.

But a ListBox of an UserForm can solve it, if you want.
 

rrocco

New Member
Joined
Sep 5, 2005
Messages
13

ADVERTISEMENT

Thanks Erdinç,

I think it will be hard without VBA :)


Cheers,
Rodolfo
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Record a macro to copy-paste the data in col. A to another blank column, then copy-paste the data in col. B to the end of that data, so on and so forth for all of the data. You'll now have a single column of data that you can use as the validation list (as long as you have less than 65536 rows).
 

rrocco

New Member
Joined
Sep 5, 2005
Messages
13

ADVERTISEMENT

yes Barry, I supposed it will be easier than that.

Thanks,
Rodolfo
 

rrocco

New Member
Joined
Sep 5, 2005
Messages
13
Erik,

It will be useful in other scenarios, due my data exceded 255 characters.

Thanks any way
 

Forum statistics

Threads
1,136,586
Messages
5,676,671
Members
419,642
Latest member
Hyakkivn

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