join four columns in one array

rrocco · Oct 19, 2006

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

Erdinç E. Karaçam · Oct 19, 2006

Hi,

Is Vba necessary?

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

On F1 cell:
Data | Validation | List:

Code:

=$E$1:$E$10

Ok?

rrocco · Oct 19, 2006

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 · Oct 19, 2006

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 · Oct 20, 2006

Thanks Erdinç,

I think it will be hard without VBA

Cheers,
Rodolfo

Barry Katcher · Oct 20, 2006

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 · Oct 20, 2006

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

Thanks,
Rodolfo

erik.van.geit · Oct 20, 2006

Hello,

take a look here
see Damon Ostranders solution
http://www.mrexcel.com/board2/viewtopic.php?p=1102760
edit the range to your needs

kind regards,
Erik

rrocco · Oct 20, 2006

Erik,

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

Thanks any way

join four columns in one array

rrocco

New Member

Excel Facts

Erdinç E. Karaçam

Board Regular

rrocco

New Member

Erdinç E. Karaçam

Board Regular

rrocco

New Member

Barry Katcher

Well-known Member

rrocco

New Member

erik.van.geit

MrExcel MVP

rrocco

New Member

Similar threads

Forum statistics

Share this page

join four columns in one array

New Member

Excel Facts

Board Regular

New Member

Board Regular

New Member

Well-known Member

New Member

MrExcel MVP

New Member

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