Linking Drop Down Lists across Worksheets

Using Office 2003.

I have created two workbooks, one which is a master price list (MasterPriceList.xls) and another which is a pricing sheet template that will be used to calculate many different products (Pricing.xls)

I have used a VLOOKUP across the workbooks (thanks to those who helped me with that) but I cannot get a drop list to work across two worksheets. When I enter the source in the "refers to" box, I get a notice saying that you can't use data validation across two workbooks. However, this was the same error message I got when I was incorrectly inserting the reference source for the VLOOKUP function, so I don't believe it Also, there are several tutorials on the web that say this can be done. However, none of them seem to work for me.

I cannot open the Data Validation box and get to the other open worksheet, so I can highlight the area I want, with the range I want to drop down. Until I close the data validation box, I cannot get out of that sheet. I have used cell ranges as well as named ranges.

When I type in a name, I get only the text I entered in the source reference box appearing on the sheet, i.e., the drop down box will only show "=C:\Documents And Settings\Allen\My Documents, etc" it doesn't seem to recognize it as a source.

Any ideas? I thank you in advance for your suggestions and guidelines.

Allen

Try the technique explained here. This site hasn't failed me yet:

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

Well, the big warning at the top might be an indicator:

"For data validation to work, the workbook which contains the list must be open, in the same instance of Excel. You could create the list in a workbook that is always open, but hidden, such as the Personal.xls workbook."

This is the reason I never do this. VLOOKUP is perfectly suited to do what you want and doesn't require all the gymnastics. PLUS, it works with closed workbooks.

You don't have to give up on the drop list, you just need to form that list from inside the workbook.

There are some hightech solutions for getting the column of part #s from the second workbook to duplicate in first workbook, but the simplest is:

1. Bring over the part numbers into an extra sheet on your current workbook (Sheet3).
In the first cell on the trash sheet, enter a formula like this:
=IF([Book2.xls]Sheet1!A1<>0,[Book2.xls]Sheet1!A1,0)
...and copy that down far enough to get your whole list. Don't worry about the zeros at the bottom, that just leaves room for the list to grow on its own.

2. Create a Dynamic Name range from these number that exclude the zeros automatically
Insert > Name > Define
Names in Workbook: PartsList
Refers to: =INDEX(Sheet3!$A:$A, 1):INDEX(Sheet3!$A:$A, COUNTIF(Sheet3!$A:$A,">"""))

3. Then set up your validation list =PartsList

I don't read anything in your post that I didn't cover above. Step #2 shows you the formula needed to make your PartsList named range dynamic. It will only show the values above 0 from the range, I promise.

The garbage sheet itself should have enough extra cells in the copy range (Step #1 above) to handle your expansion, and as a result the cross-sheet references WILL result in zeros at the bottom. Don't fret it, it's a garbage sheet, right? You can even hide that sheet so no one even gets to see it and complain.

As for your signature, click on the USER CP link at the top of any page, then click on Edit Signature on the left side-panel.

Very glad you got it worked out.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

Thanks.