Can you do a data validation between two spreadsheets?

**Jtwanabe** · 02-28-2016, 04:08 PM

I building an estimating program for a printing business and I want to put things like paper cost, Customer info, etc on their own spread sheet
and reference it.

thanks in advance

**mrice** · 02-28-2016, 04:19 PM

No sure that you can do this directly.

One alternative is to have your workbook automatically take a copy of the data in the reference workbook when it is opened and then drive your data validation of this copied sheet.

**JeteMc** · 02-28-2016, 04:31 PM

If you are asking if Data Validation can take values from a list on a different sheet in the same workbook then yes. Take a look at the attached file to see how: Data Validation.xlsx Notice that the DV for Sheet1!A1 is set to a list on Sheet 2 and the DV for Sheet1!B1 is set to a list on Sheet 3.
Let me know if you have any questions.

**Jtwanabe** · 02-28-2016, 06:30 PM

JeteMc ...... I looked for a reference to other work sheets in your spreadsheet but couldn't see it. Let me phrase what I am trying to do and the issue I'm having. When I try to make a drop down menu for say paper stock from a list on another worksheet. I first select the cell than go to the
data validation and choose list. Then when I pick the source and try to select another worksheet I get a "ding" and cannot select it. I hope that sort of makes sense. thanks

**sandy666** · 02-28-2016, 07:14 PM

Data Validation List from Another Workbook

**Ochimus** · 02-28-2016, 07:50 PM

Jtwanabe,

Open both the attached files.

In the "Paper Order" file, the "Customer" droplist in C3 of the Order Form worksheet shows the list in the Customer worksheet, as you will see if you click "Data Validation"

But the "Paper Size" droplist in C5 is linked to the separate "Paper Size" file - and you MUST have both files open for it to work.

To create the Drop List from another file,
(a) Go to the file containing the list you want to use (in this case the Size list in the Paper Size file.
(b) Highlight the list, click "Formulas", and "Create from Selection" -I named it "Size" in the attachment.
(c) Now go to the file where you want the Droplist (in this case the Paper Order file)
(d) Click "Formulas" - "Name Manager" -"New" and give it a name (e.g. "MyList")
(e) In the "Source" bar, you type in the name of the other file, and the range name you selected in that book (e.g."='Paper Size.xlsx'!Size")and close the popup.
(f) Go to the cell where you want the DropList, and set the Data Validation to "=MyList".
Using that "indirect" method avoids Excel telling you you cannot access a validation list from another workbook

Hope that solves your problem.

Ochimus