list from empty data cell source

[rldjda]

I have 2 sheets. I would like Sheet 1 A1 to have a list from items in Sheet
2 A1:100. The only problem is, Sheet2 A1:100 starts off blank until there
are items listed later.

For example, Sheet1 A1:100 "WILL" contain a list of cars. This list will
continuously be added to on a daily basis. I would like to click on Sheet2
A:1 and be able to select from a list of cars that we have so far. How can I
do this?

 

[T. Valko]

I assume that when you do add items to Sheet2 A1:100 you will start in A1
and add items to make a contiguous range.

Create this named formula:

Insert>Name>Define
Name: CarList
Refers to:

=Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$100,COUNTA(Sheet2!$A$1:$A$100))

OK

On Sheet1 select cell A1
Goto Data>Validation
Allow: List
Source: =CarList
OK

See this 5 min video: