I have 2 different columns on a spreadsheet, one is "who" and the other is
"contractor". I would like to have a drop down on a separate spreadsheet that
would pull back both the who and contractor names. I know I have to define a
name but I can't figure out how to define 2 separate columns into one name.
BTW, the columns must stay separate for various reasons so I can't just make
one large list... Any help is appreciated!!!

On Jul 22, 1:06 pm, ChrisP wrote:
I have 2 different columns on a spreadsheet, one is "who" and the other is
"contractor". I would like to have a drop down on a separate spreadsheet that
would pull back both the who and contractor names. I know I have to define a
name but I can't figure out how to define 2 separate columns into one name.
BTW, the columns must stay separate for various reasons so I can't just make
one large list... Any help is appreciated!!!

Make a 3rd column somewhere, hidden perhaps, with the 2 names
concatenated.
=CONCATENATE(A1,B1)
=A1&B1

Then reference this column for your drop down.

Fast and Quick - you can have one or the other, but not both. Sorry. In
this case you can use one name to define both columns, but when you go to use
Data Validation to create a list, it's going to tell you that it can only use
one column at a time.

But just FYI - here's how to create a name that includes non-contiguous cells:
Select a cell/group of cells, then hold down the [Ctrl] key while selecting
other cells on the sheet. When you have all selected that you want
referenced as a name, type the name into the 'Name Box'. Don't forget to
terminate that entry with the [Enter] key. To test, click in any cell, then
choose the name from the Name Box and observe that all the cells in the named
range are selected.

To continue on - I presume that each "who" entry is unique and has a
specific "contractor" listed on the same row, even though in a different
column? If this is true, you could use 2 cells on the other sheet. One
would be a drop down created by referencing the "who" list, while the one
next to it could use VLOOKUP() to find the contractor associated with the who
chosen.

How to set up the data validated cell using a list from the other sheet:

Choose all the cells in the "who" list and give it a name; for this example
we will call it "myWhoList". Go to the cell where you want the data
validation used and set it up to use a List and in the 'Source' entry box
enter
=myWhoList

Again for example's sake, let assume you set this data validation up in cell
A6 on a sheet and you now need the contractor to show up in B6. Back on the
other sheet, the myWhoList goes from A2 to A199 while the contractor list
goes from D2 to D199 and that sheet is named[Lists]. In B6 you could set up
this formula:
=VLOOKUP(A6,Lists!A$2:D$199,4,False)
if this starts giving you #N/A errors, then change it to:
=IF(ISNA(VLOOKUP(A6,Lists!A$2:D$199,4,False)),"",V LOOKUP(A6,Lists!A$2:D$199,4,False))
and that'll keep those from messing up the sheet's neat appearance.

HTH

"ChrisP" wrote:

I have 2 different columns on a spreadsheet, one is "who" and the other is
"contractor". I would like to have a drop down on a separate spreadsheet that
would pull back both the who and contractor names. I know I have to define a
name but I can't figure out how to define 2 separate columns into one name.
BTW, the columns must stay separate for various reasons so I can't just make
one large list... Any help is appreciated!!!