Data Validation in a List

[maic15]

Hi All,
I'm working in sheet1 and trying to create a list from values in sheet2 column A:A.
The data valation function will not allow me to create a list using values from another worksheet.

Is there a way to call the values from sheet2 into sheet 1 cell A1: A5000?

 

[Andrew Poulsom]

Name the range eg MyList and use that name as the source:

=MyList

 

[maic15]

how do I set the values of Mylist? I'm a little confused

 

[Brian from Maui]

how do I set the values of Mylist? I'm a little confused

A couple of ways,

Go to Insert/Name/Define, in the Names in Workbook, enter MyList, in the Refers to, enter =Sheet1!A1:A100, click Add.

Or highlight A1:A100 and in the Name window enter MyList and press enter.

 

[maic15]

Cool! One more question. If a 100 cells do not contain a value in my list, but will tomorrow, how do I prevent null values from appearing in my drop down. For example, A2:A100 is my list, but only A2-A30 contain values right now. How do I prevent the blanks from showing up?

 

[Brian from Maui]

Cool! One more question. If a 100 cells do not contain a value in my list, but will tomorrow, how do I prevent null values from appearing in my drop down. For example, A2:A100 is my list, but only A2-A30 contain values right now. How do I prevent the blanks from showing up?

What kind of values, text or numbers?

=Sheet2!!$A$2:INDEX(Sheet2!$A$2:$A$65536,MATCH(9.99999999999999E+307,Sheet2!$A$2:$A$65536))

For numbers, substitute the 9.99 bit with Rept("Z",255) for text

 

[maic15]

The cell contains text or is null.

 

[Brian from Maui]

The cell contains text or is null.

Then,

=Sheet2!$A$2:INDEX(Sheet2!$A$2:$A$65536,MATCH(REPT("Z",255),Sheet2!$A$2:$A$65536))

using the Insert/Name method

 

[maic15]

Insert/Method is not an option for me. I have Name/ Define, Paste, Create, Apply, Label

Does one of the following = Method?

 

[Brian from Maui]

Insert/Method is not an option for me. I have Name/ Define, Paste, Create, Apply, Label

Does one of the following = Method?

Insert/Name/Define method which I described earlier in the thread.