drop-down list error

silvanet

New Member
Joined
Nov 7, 2007
Messages
20
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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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).
 

silvanet

New Member
Joined
Nov 7, 2007
Messages
20
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Nov 7, 2007
Messages
20
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top