drop-down list error

[silvanet]

I followed the Excel Help directions to the letter creating a drop-down list with an outside spreadsheet reference, but when I went to finish, I got a pop-up error message saying "You may not use references to other worksheets or workbooks for Data Validation criteria." What's that all about? The instructions clearly say that you CAN use references to other worksheets or workbooks!

 

[RoryA]

Where are these instructions? You cannot use references to other sheets unless you use a defined name in the activeworkbook and then refer to that defined name in the data validation dialog. (might be different in Excel 2007, I can't recall offhand).

 

[VoG]

THe workbook containing the Data Validation list must be open - see http://www.contextures.com/xlDataVal05.html

 

[silvanet]

Where are these instructions? You cannot use references to other sheets unless you use a defined name in the activeworkbook and then refer to that defined name in the data validation dialog. (might be different in Excel 2007, I can't recall offhand).

Click on Help, search on "Create a drop-down list from a range of cells"... it reads

"If you want to use another worksheet or another workbook, do one of the following:

1. Use a different worksheet in the same workbook ..."

which was NOT what I wanted to do.

It goes on,

"2. Use a different worksheet in a different workbook Type the list on that worksheet, and then define a name with an external reference to the list."

[now, that is what I wanted to do, and the instructions imply clearly that you can do so]

How?

"Open the workbook that contains the list of drop-down entries.
Open the workbook where you want to validate cells, point to Name on the Insert menu, and then click Define.
In the Names in workbook box, type the name, for example, ValidDepts.
Accept the default value in the Refers to: box, and then click OK.
In the Refers to box, delete the contents, and keep the insertion pointer in the box.
On the Window menu, click the name of the workbook that contains the list of drop-down entries, and then click the worksheet that contains the list.
Select the cells containing the list.
In the Define Name dialog box, click Add, and then click Close. "

If you follow the instructions, you will see that it does not work. I'm using Excel 2003, SP3.

The error you will get is exactly as I mentioned.

 

[silvanet]

THe workbook containing the Data Validation list must be open - see http://www.contextures.com/xlDataVal05.html

See my reply to rorya ... Contextures is wrong ... you CANNOT reference external spreadsheet.

 

[VoG]

See my reply to rorya ... Contextures is wrong ... you CANNOT reference external spreadsheet.

I will test this when I'm on another machine. Currently I'm using XL 2007 which allows a lot more by way of Data Validation. I'll post back.

 

[RoryA]

OK, those instructions are missing a step. You need to define a name in the source workbook as well, then refer to that name when creating the name in the workbook where you want to use the validation list. So if you define a name in WorkbookB.xls called ListData that refers to the range you want to use as the list, then in WorkbookA to use this as the Validation source, you need to define a name (say MyList) that refers to =WorkbookB.xls!ListData and you can then use =MyList in the Data Validation source box.

 

[erik.van.geit]

Hi,

I've got a working example of a source.xls + destination.xls
feel free to email me
subject line: validation: source.xls + destination.xls

kind regards,
Erik

 

[silvanet]

OK, those instructions are missing a step. You need to define a name in the source workbook as well, then refer to that name when creating the name in the workbook where you want to use the validation list. So if you define a name in WorkbookB.xls called ListData that refers to the range you want to use as the list, then in WorkbookA to use this as the Validation source, you need to define a name (say MyList) that refers to =WorkbookB.xls!ListData and you can then use =MyList in the Data Validation source box.

No, nothing is "missing." all the steps are in the actual help screen. I didn't copy all of it because it would be too long. I only copied the relevant part to show that there is supposed to be a way to do it. If you follow the instructions to the letter (including what you mention), you will still get the error I mentioned. It just does not work. Please try it before you respond again. Try to make your validation list come from another spreadsheet (not just another worksheet "tab" in the same spreadsheet).

 

[erik.van.geit]

there are 3 ways to solve this

Definitions
Source = workbook containing the data
Destination = workbook containing the validated cells

1. VBA
in Destination workbook module

Option Explicit
 
Private Sub Workbook_Open()
    Dim SourceRange As Range
 
    Application.ScreenUpdating = False
    'change this path to a valid path
    Set SourceRange = Workbooks.Open(Me.Path & "\Source.xls", _
        False, True).Worksheets(1).Range("B2:B21")
 
    With Me.Names.Add("ListItemsVBA", Me.Sheets(1).Range("E3").Resize(SourceRange.Cells.Count))
    .RefersToRange.Value = SourceRange.Value
    End With
 
    SourceRange.Parent.Parent.Close False ' close the source workbook without saving changes
    Application.ScreenUpdating = True
End Sub

STEPS in code:
opening the Source
getting the values
putting them in a named range somewhere in the Destination
closing the Source

2. named range method 1
all cells in the named range have formulas refering to Source
=[Source.xls]Sheet1!$B2
=[Source.xls]Sheet1!$B3
etcetera

source must not be opened
you will get a popup to ask to update the links
not recommended when it is a long list

3. named range method 2
defining the named range like this
='D:\herethepath\[Source.xls]Sheet1'!$B$2:$B$21
this will only work when the other workbook stays open: closing will immediately clear the list

these methods are all demonstrated within the workbooks which you can "email" me for (not PM!)

I prefer the VBA method because error handling can be added: a message can be displayed warning you if the source is not available and even do a search for it or present an alternative.

kind regards,
Erik