will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Data Validation List From Different Workbook
In Excel, you can create a drop down Data Validation list, using a list from another workbook as the source.
However, for the data validation to work, the workbook that contains the list must be open, in the same instance of Excel. Users will have to open both the workbooks -- the one with the drop down lists, and the workbook with the original source list.
The following instructions are for Excel 2007. There are similar instructions to create a data validation list from a different workbook in Excel 2003.
Create the Source List
In this example, you have a workbook named DataValWb.xlsx, which contains a list of customer names. This list is in a range named CustName. (For instructions on creating a named range, refer to Naming Ranges.)
Create a Named Reference to the Source List
- Open the workbook that contains the source list -- DataValWb.xlsx in this example.
- Open the workbook in which you wish to use the list in Data Validation, or create a new workbook.
- On the Ribbon, click the Formulas tab, then click Define Name.
- Type a name for the List, e.g. MyCustList
- From the Scope drop down, select Workbook
- In the refers to box, you’ll type a reference to the named range in the source workbook:
- Type an equal sign, then the source workbook name and extension
- Next, type an exclamation mark, followed by the range name in the source workbook.
- In this example, the completed reference is:
=DataValWb.xlsx!CustName - NOTE: Type an apostrophe (') at the start and end of the referenced workbook name, if it contains a space character. For example:
='DataVal May10.xlsx'!CustName
- Click OK
Create the Dropdown List
- Select the cells in which data validation will be set.
- On the Ribbon, click the Data tab, then click Data Validation
- In the Allow box, choose List
- In the Source box, type an equal sign, then the list name, e.g.: =MyCustList
- Click OK
Use the Data Validation List
To use the data validation drop down lists, both workbooks must be open.
- Open both workbooks -- the one that contains the drop down lists, and the workbook that contains the original source list.
- Select an item from the data validation drop down list.
____________________
May 13th, 2009 at 8:40 am
Debra:
What happens if this workbook is used to fill in a form, for example a report, where the validation is a client name, and the workbook is given to someone else, who dose not have the workbook with the data source. Will the data field still be filled in, or will an error message show up?
May 13th, 2009 at 12:03 pm
Stan, without the source book open, the user should see a prompt about updating links. They could click No to that prompt.
None of the selected values would be affected, but the drop down lists wouldn't work.
May 15th, 2009 at 9:29 am
If I needed to share it with other people, it seems like I would just place the data in the same workbook on a different sheet.
May 15th, 2009 at 11:20 am
Rick, that's what I'd do too, but a surprising number of people ask how to use a list in a different workbook.
May 25th, 2009 at 9:17 pm
[...] Fijate en este post en el blog de la canadiense Debra Dalgleish. Contextures Blog Data Validation List From Different Workbook [...]
May 29th, 2009 at 11:29 pm
http://blog.contextures.com/archives/2009/05/12/data-validation-list-from-different-workbook/
I am trying to do the same thing as instructed on the above weblink, to create my data in another work book, named and saved it, but when I go for another work book for data validation it keep saying that there is no such name in the range. Help ??
July 18th, 2009 at 3:02 am
I use this method to share some works with some workers. Would you be so kind to explain how exclude any worker after using Combo Box (Excel 2007!)
September 7th, 2009 at 4:18 pm
It simply does not work in excel 2007, I have tried this many times but still it does not!
Are we going backwards with office 2007?
September 7th, 2009 at 7:08 pm
Pat, it works for me in Excel 2007. What happens when you try it? Do you get an error message?
September 8th, 2009 at 3:07 am
In the help facilities it does stipulate that validation cannot be done with an external workbook! The only way round is to create a macro to open this external workbook, hide it, to pick up the data from (list).
I have been working with vb, vba, access, excel for 18 years!
September 8th, 2009 at 8:32 am
Yes Pat, you're right, this technique only works if the workbook with the original list is open too.
October 2nd, 2009 at 2:25 pm
I was struggling to make this work Pat until you revealed that both w0rkbooks have to be opened. You would think the original author would have noted this. Anyway, how do you trigger your macro? Could it be made to run automatically when the workbook is opened? I don't want to have to issue intructions to click a button if I can help it.
October 2nd, 2009 at 2:35 pm
Paul, glad you were able to get this working. In the article, I did mention that the other workbook has to be open -- it's in bold, in the sentence under the first screenshot. Maybe I'll add that as a note, at the end of the instructions too, to make sure that everyone see it.
You could record a macro as you open the other workbook. Then, add that code to the Workbook_Open event code, for the workbook with the data validation drop downs.
October 3rd, 2009 at 7:22 am
Hi Deborah, I assumed you meant that both workbooks had to be open in the same instance of excel whilst setting up the link. It wasn't clear that the link would then essentially break when the source workbook is closed.
I'm not sure how I would add the macro to the Workbook_Open event code - would this have to be done in VBA? Can you give me an example of how you would do this or point me in the direction of a tutorial?
Many thanks!
May 3rd, 2010 at 9:34 pm
CAN U HAVE 10 SHARED WORKBOOKS USING A SINGLE SOURCE WORKBOOK, FOR A DROPDOWN MENU;
BUT I NEED TO HAVE LIKE 5 BOOKS OPEN AT A TIME FROM DIFFERENT COMPUTERS OVER A NETWORK. PLUS EACH ONE NEEDS TO HAVE A FUNCTIONING DROPDOWN OPTION.
CAN THIS BE DONE?
May 11th, 2010 at 8:09 am
Please tell me about data validation in excel 2007
May 11th, 2010 at 3:28 pm
Not sure if Lokesh's post is spam or not, but here is a page I created on Data Validation in Excel 2007 and will work in Excel 2010.
http://bohr.wlu.ca/cp212/201005/excel_data_validation.htm
May 11th, 2010 at 3:34 pm
Thanks Rick, and I'm not sure what Lokesh wants to know, because this blog post showed the steps in Excel 2007.
And nothing has really changed from earlier versions, except the command is on the Ribbon now, instead of the menu bar.
June 21st, 2010 at 8:37 am
Thanks Rick Henderson.
I need sm details reg. macros. I'm using xl for create invoice, and I want to save all invoices in a single sheet. possible?
August 4th, 2010 at 5:30 am
Debra,
Thanks for this solution - it saved a lot of hassle for me.
One thing that you could ad to the instructions:
You have to use apostrophe (') in the reference workbook name if it has a space.
Example: ='DataVal May10.xlsx'CustNames
(It might be obvious to most of you, but not for the less advanced programmers)
Thanks again!
August 4th, 2010 at 8:59 am
Laz, thanks -- I've added that note to the instructions.
August 18th, 2010 at 12:58 pm
I followed the steps, have both workbooks open, but I still get the error message saying it cannot reference to another workbook.
August 26th, 2010 at 5:31 am
Hi Anderson,
it happened to me too. My problem was that I kept copying an invalid link that used square brackets [] at the beginning and at the end of the file name and also reported the Sheet name. Example: ='[DataVal May10.xlsx']Shhet1'!CustName. I deleted the square brackets and the sheet name and everything worked fine. Don't know if it's the same for you, but give it a try.
Follow step by step the guide (without using any short cut or copy and paste) and you should be fine