Creating dynamic reference from another workbook

[dpaton05]

I want a drop down list in one workbook that references a dynamic list in another workbook ie it may have entries added to it so I can't hard code the reference?

If I select data validation for a cell, I set it to allow a list but what do I put in the source for the list to be from another workbook and for it to be dynamic.
This list will start in A2 and go down. There is nothing under it.

Thanks

 

[mikerickson]

Define a dynamic named range for that list, perhaps

Name: myList
RefersTo: [Book2]Sheet1!$A2: INDEX([Book2]Sheet1!$A:$A, COUNTA([Book2]Sheet1!$A:$A, 1)


create this in Book1 (the book with the drop down)

Then use that =myList as the source for the list.

 

[dpaton05]

Does book2 need to be book2.xlsm or just book2?

 

[dpaton05]

This is the code if I select just that list but is not dynamic. It says that this type of reference cannot be used in a data validation formula.

=[client_list.xlsm]CSCs!$A$5:$A$96

This is the formula I tried to adapt from your suggestion but I get the error message of there is a problem with this formula.

=[client_list.xlsm]CSCs!$A2:index([client_list.xlsm]CSCs!$A:A, COUNTA([client_list.xlsm]CSCs!$A:$A,1)

 

[MARK858]

You are missing a closing bracket and are you putting it in as a defined name as Mike stated?

 

[dpaton05]

I got mixed up,

here is the source for the named range I try to enter

=[client_list.xlsm]CSCs!$A2:index([client_list.xlsm]CSCs!$A:[SIZE=6][B]A[/B][/SIZE]), NPSS_quote_sheet!$1:$1048576COUNTA([client_list.xlsm]CSCs!$A:$A,1)

I get the error message of you've entered too few arguments with the A above highlighted.

 

[MARK858]

Do you see a bracket after the A in Mikes formula? and what is the NPSS_quote_sheet!$1:$1048576 there for?
The missing bracket in Mike formula should be at the end so as far as I can see your formula in the defined name should be...

=[client_list.xlsm]CSCs!$A2:INDEX([client_list.xlsm]CSCs!$A:$A, COUNTA([client_list.xlsm]CSCs!$A:$A, 1))

 

[dpaton05]

Thank you Mark.

 

[dpaton05]

Not sure how the extra code got in there.

 

[dpaton05]

Just found another problem. When I enter in this formula

=[client_list.xlsm]CSCs!$A2:INDEX([client_list.xlsm]CSCs!$A:$A, COUNTA([client_list.xlsm]CSCs!$A:$A, 1))

...into the "refers to" box in the name manager for editing the name, then close and reopen the window, it will still be the same.

However, if you save, close and reopen the wb, that same formula above has turned into a specific reference of where the file is located on the HDD so you can't move the file around as the location has been hard coded in.

Do I need to use vba to enter the formula back in to the 'refers to' box each time the wb is opened?