create a drop down list with the source from a different workbook

Hi,
I tried to create a drop down list (Data > Validation > List) with the 
source from a different work book (If you type the list in a different 
workbook, define a name with an external reference to the list.). I followed 
the instructions given in the Microsoft Online Assistant.
Iam not able to create the same as iam a getting a message stating that "You 
may not use references to other worksheets or workbooks for Data validation 
criteria."

0
Sampath (1)
1/8/2005 7:35:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
347 Views

Similar Articles

[PageSpeed] 16

Try Debra Dalgleish's web site on this subject:

http://www.contextures.com/tiptech.html

Scroll down to "D", and see all the pages on "Data Validation".
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Sampath" <Sampath@discussions.microsoft.com> wrote in message
news:CE5BAF95-2226-48DF-BC5C-460BC1584AD8@microsoft.com...
Hi,
I tried to create a drop down list (Data > Validation > List) with the
source from a different work book (If you type the list in a different
workbook, define a name with an external reference to the list.). I followed
the instructions given in the Microsoft Online Assistant.
Iam not able to create the same as iam a getting a message stating that "You
may not use references to other worksheets or workbooks for Data validation
criteria."


0
ragdyer1 (4060)
1/8/2005 7:54:39 PM
Hi


Add a sheet into your workbook, and using links,  mirror the list from
another workbook into this sheet. I.e. into cell A1 enter the formula
=IF('DriveLetter:\Path\[AnotherWorkbook.xls]SheetWithList'!A1="","",'Drive:\
Path\[AnotherWorkbook.xls]SheetWithList'!A1)
and copy it so whole list is mirrored.

Define a dynamic named range based on mirrored list (I assume it is in
column A, with header in cell A1), like
MyList=OFFSET(MirrorSheet!$A$2,,,COUNTIF(MirrorSheet!$A:$A,">""")-1,1)

Select the cell/range you want to be formatted as data validation list,
select Data.Validation.List from  menu, and nto sourve field enter
=MyList

(replace all worksheet and workbook names etc. with ones used by you, of
course)

Arvi Laanemets



"Sampath" <Sampath@discussions.microsoft.com> wrote in message
news:CE5BAF95-2226-48DF-BC5C-460BC1584AD8@microsoft.com...
> Hi,
> I tried to create a drop down list (Data > Validation > List) with the
> source from a different work book (If you type the list in a different
> workbook, define a name with an external reference to the list.). I
followed
> the instructions given in the Microsoft Online Assistant.
> Iam not able to create the same as iam a getting a message stating that
"You
> may not use references to other worksheets or workbooks for Data
validation
> criteria."
>


0
1/8/2005 7:57:25 PM
Reply:

Similar Artilces:

Using same SSRS installation to support two different CRM installs
We are implementing a second instance of CRM 4.0. It is for a different business so CRM will be installed on a different server than our current instance. Multihoming is not an option. In our current implementation our Reporting Services installation is on a different box than CRM. We would like to use the same SSRS server for reports for the new CRM installation. Is this possible? Our Reporting SErvices server is on a different machine also. -- Leo That should work just fine aslong as the organization names are different. Otherwise both instances will try to use the same folder...

Web form that drops into access database
I want to do something that I feel is very simple; however, I have no knowledge of how to do it. I want to have a form on a website that drops the data from the form into an access database. I have a decent amount of experience with web design, and would just like somewhere to start. Any help would be greatly appreciated. Thanks, Michael <mgreer65@gmail.com> wrote in message news:e6a7c688-272b-4219-8241-74c3e3e7b2a2@e25g2000prg.googlegroups.com... >I want to do something that I feel is very simple; however, I have no > knowledge of how to do it. I want to have a form on a w...

Creating mailboxes for users in a child domain
I am attempting to provide the ability for administrators in a child domain to create Exchange mailboxes on an Exchange server in the parent domain for users of the child domain. I have installed the Exchange Management Tools on the DC of the child domain, and delegated Exchange Administrator privileges to the administrator of the child domain. When creating the accounts the process to create the mailbox completes successfully on that DC (as does adding a mailbox to an existing user account with the exchange tasks option) however, I never see the mailbox on the server, and when creating an O...

security group/distribution list administration
please take a look at the thread below. i posted this earlier today to the AD group. someone suggested to check here as well to possibly find a workaround instead of having to create and maintain two separate groups. thanks. ------------------------------------------ So far I know you have to create two groups, but check the exchange group, they may have some smart tricks. -- Regards Christoffer Andersson No email replies please - reply in the newsgroup ------------------------------------------------ http://www.chrisse.se - Active Directory Tips "toto" <toto@discussions.mic...

Bug! Error Bars Inherited from Another Workbook
Got an odd one here. I have suite of macros that create a workbook with a number of sheets, containing charts from a number of separate workbooks. The charts show a number of series, including a mean with custom error bars, taken directly from two columns in the source data, spec for the + bars ='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the following column for the - bars. I run the macro once, and save the resultant chart-filled workbook as DirectoryA/FileA.xls. I change the data in the source workbooks, rerun the macro and save the results as DirectoryB/FileB...

Shortcut for copying formulas referencing different worksheets
-------------------------------------------------------------------------------- I am looking for a shortcut. I am copying the same forumula from Row 1 down the page, but where Row 1 references the next worksheet, Row 2 references the following worksheet, and so on. Is there an easy way to do this without having to go to each worksheet, find the cell and click on it? TIA -- bhigdon ------------------------------------------------------------------------ bhigdon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23686 View this thread: http://www.excelforum.com/s...

Passing an Array created in a Function or Subprocedure back to the calling Subprocedure
I want to have a Function or Subprocedure that can create an array with 52 specific values in it. I then want to call this Function or Subprocedure from a main Subprocedure and be able to access the contents of the array. Everything can be of type variant for the purposes of this exercise. Can anybody help, please? This worked ok for me: Option Explicit Sub testme() Dim myArr As Variant Dim iCtr As Long myArr = BuildArray(myArr) For iCtr = LBound(myArr) To UBound(myArr) MsgBox iCtr & ". " & myArr(iCtr) Next iCtr End Sub Function Buil...

Can't create file: FAX.TIF
I keep getting this error message when I try to open faxes that come down over our server. Here is the entire message. Can't create file: FAX.TIF. Right click the folder you want to create the file in, and then click Properties on the shortcut menu to check your permissions for the folder. I have checked the properties for the Inbox and for the Office Document Imaging and all of that is fine. We were able to open faxes fine, then one day a Smiley bar appeared in Outlook and then we were not able to open the faxes anymore. Also, I have removed Outlook and reinstalled. I hav...

Creating my first callout
Hi I'm just trying out how to make a simple callout but before I can do that I must add a reference to the Microsoft.Crm.Platform.Callout.Base.dll. How di I make that reference? Hi Christian, See http://msdn2.microsoft.com/en-us/library/aa680652.aspx. The entire documentation is included in the CRM SDK, which is available at http://www.microsoft.com/downloads/details.aspx?FamilyID=9c178b68-3a06-4898-bc83-bd14b74308c5&DisplayLang=en. Please post developer related questions to microsoft.public.crm.developer. Good luck! -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://w...

Outlook 2003 Distribution List error
System running XP SP3 My customer has a Distribution List (5 entries), and it was working correctly for some time. Now, when she sends to the List, there are two additional names included from her address book. Editing and/or expanding the list does not show these names.... Ideas? Where to look? Thanks in advance. Grov ...

Business Fax is also in my contact list
When I try to send out a mass e-mail to my suppliers, I get their business fax in my contact list. Is there any way to deactivate this. Thanks for any help. ...

Sitemap works ok but asp:Menu drop downs are blank
My drop downs for asp:Menu are simply white rectangles. However my site map works ok. Do you know what might cause that? Thanks Cal Who wrote: > My drop downs for asp:Menu are simply white rectangles. > > However my site map works ok. > > Do you know what might cause that? > > > > Thanks Maybe one of these: Transparent drop-down Menu and disappearing menu text - http://windowsxp.mvps.org/transmenu.htm Several dialog boxes are blank: http://support.microsoft.com/kb/831430 "About" box is completely blank; Una...

How do I link Excel pages to a different master Excel workbook?
I am trying to take part lists from different assemblies and link them to a master part list. Ideally one sheet from the assembly part lists will have many pages and be linked to a sheet in the master part list with the name of that specific assembly. I am operating on Midrosoft Office Version 2003. open both the master and your part list on your master if you set a cell to (="name of part list book"!A1) you can do that by clicking any cell on the other workbook with them both open its just like a formula on the sheet only instead it has the workbooks name first i hope thi...

How can I enarge the font size in a drop-down list?
1. How can I enarge the font size in a drop-down list? 2. How can I prevent someone from changing or deleting the formula in a cell? When I try to protect the cell it prevents the formula from being executed. Formulas still work when protected. If you are using data>validation you cannot change the font, you can use a macro that zooms http://www.contextures.com/xlDataVal08.html#Font -- Regards, Peo Sjoblom "BobH" <BobH@discussions.microsoft.com> wrote in message news:A63E65DE-CFB3-40FA-B1D8-0E576A436885@microsoft.com... > 1. How can I enarge the font size i...

Auto complete form field from separate list
I can't seem to find the answer with the paperclip. I'm designing a form in a worksheet. One field intended for the user t input a "city name" We may be using 20 or 30 cities in total. It woul be great just to have to type a few letters and smack enter. I kno I'm a bonehead cause I can't figure it out, because it's probabl something simple I just haven't figured out yet -- famdaml ----------------------------------------------------------------------- famdamly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2938 View this thre...

"No more new fonts may be applied in workbook"
I have a workbook with a number of worksheets and charts. However, I am receiving the error message "No more new fonts may be applied..." when I am cutting and pasting data into my "datasource" worksheets (the data that feeds the charts on the other worksheets). I'm not adding anything to the worksheet - it's just an updated version of the data. Do I need to remove the chart formatting so I no longer receive this message? Thanks, ...

Budget Wizard-option to create multiple excel files
Allow for an option during the budget wizard to create multiple excel spreadsheets breaking by department . This would prevent a client from having to export a budget for each department or manipulating the large spreadhseet into many spreadsheets ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agre...

Choosing parameters from a list.
Hi, I have created a form with several combo boxes. In the form I have built a macro that opens a report with interactive parameters using the WHERE clause. I would like to have the list from the combo boxes to be an option to choose instead of the user typing in the paramater. Also if no parameter is entered, how do I ensure that all data appears in the report. Right now if nothing is entered the report is blank. -- Thank you, Mich If you leave the critiera out of the report's source query, and build up the WhereCondition for OpenReport, you can test if the text box IsNull()...

creating a resource appendix
Hi, I'm trying to create an appendix for a lot of resource information. Having trouble with the text and alligning the columns. Also, I want the grid lines to remain on the printed version- any suggestions???? Thanks, Trainee You can set gridlines to print under File, Page Setup on the Sheet tab. -- Jim Rech Excel MVP "Trainee" <anonymous@discussions.microsoft.com> wrote in message news:44f201c42bab$dc10ff80$a401280a@phx.gbl... | Hi, I'm trying to create an appendix for a lot of resource | information. Having trouble with the text and alligning | the columns. ...

Having a different Excel file on Each Screen (Multiple Monitors)
HI, I've noticed with WORD I can have one word document up on one of my screens and another file in a different screen but for some reason I can't do it with Excel. Am I doing somethign wrong? Thanks Don't know that you're doing anything wrong, just maybe not enough right. Got to Window>New Window, then go to Window>Arrange and pick how you want to have the windows of your workbook positioned. You can the work back & forth, even on different sheets in each window. If you want more than 2 windowns on the same workbook, just repeat the process as necessary (do...

Using Excel as a gradebook with ability to drop lowest grade
I use Excel as my gradebook using separate worksheets for subcategory. In the test worksheet, I would like to be able to, for each student, drop the lowest test grade. This may be a different test for each student. Try this: =(SUM(B2:F2)-(MIN(B2:F2)))/(COUNT(B2:F2)-1) This will drop the lowest grade and give you the average grade after the lowest grade is dropped. This assumes that you have five grades entered in columns B through F. Hope this helps. "Teacher" wrote: > I use Excel as my gradebook using separate worksheets for subcategory. In the > test worksheet, I ...

"To:" dropdown list, Outlook 2003
Hi all, When I create a new email and type a character in the To: field, I get a dropdown list of possible recipients. Presumably, the list contains names of people I have emailed to in the past. Many of these "possible recipients" have left the company years ago. How do I get rid of the obsolete entries? - Arnie use the arrow keys to highlight the bad address, then delete keybd btn "Arnie" <NoOne@home.now> wrote in message news:OPXLd5vIJHA.4692@TK2MSFTNGP02.phx.gbl... > Hi all, > > When I create a new email and type a character in the To: fiel...

can i create a guestbook using microsoft publisher?
I am trying to create a web page with a guestbook for visitors to sign. Can I create a guestbook using microsoft publisher 2007? Quicker answers may be found at microsoft.public.publisher.webdesign ? "Jill B" <Jill B@discussions.microsoft.com> wrote in message news:ED514120-CB19-4E64-9E0B-865140EB0B84@microsoft.com... >I am trying to create a web page with a guestbook for visitors to sign. Can >I > create a guestbook using microsoft publisher 2007? Jill, No. A guestbook will require (at least in most cases) a database as well as either php or asp. Try www.sou...

How to make distribution list from database.
Hi. I have a database containing 3000 members. Every week we send a mail to all members - this is done manually. Is it possible to extract the mail address from the database and put them in a distribution list, witch we can send our newsletter to. My thought is that this can be an automatic process. Regards Morten. A query based DL could do the job. "morten" wrote: > Hi. > > I have a database containing 3000 members. Every week we send a mail to > all members - this is done manually. Is it possible to extract the mail > address from the database and put them...

how do I create a macro to select all colored cells in a worksheet
Need help writing the code for an Excel Macro that would find all cells that have any interior color so that an outer border can be added around all colored cells. Hi, Try this. I've included the code to add the borders but you can delete this if you want to do it manually. Not this will not work for conditionally formatted coloured cells Sub sonic() Dim CopyRange As Range For Each c In ActiveSheet.UsedRange If c.Interior.ColorIndex <> xlNone Then If CopyRange Is Nothing Then Set CopyRange = c Else Set CopyRange = Union(C...