Follow up question:
I cant get any of the following links below to work:
=C:\[ACT items.xlsx]items'!brands ("brands" is a defined name) =C:\[ACT items.xlsx]items'!$A$1 (just to test linking)
I also get this warning: "You cannot use references to other worksheets or workbooks for Data Validation criteria."
Which leads me to think this is not possible
Previous Answer: The link to another workbook should look like
The remote file doesn't have to be open for the link to work; but, it does have to be open for the link to update.
When I tried it, the link to a range name only worked if the range was in the same position the remote worksheet as the cell that was linked to it. That seemed a little odd, but, apparently that is the case.
You can use range names to allow data validation across worksheets in the same workbook, but, apparently not across workbooks. I am not sure why you are getting the error, since I couldn't even get the remote link into the listbox in the data validation screen.