list from empty data cell source
 

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?

list from empty data cell source
 

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:

InsertNameDefine
Name: CarList
Refers to:

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

OK

On Sheet1 select cell A1
Goto DataValidation
Allow: List
Source: =CarList
OK

See this 5 min video:

http://youtube.com/watch?v=t2OsWJijrOM


--
Biff
Microsoft Excel MVP


"rldjda" wrote in message
...
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?