I have two sheets that each contain a column of data that I would like combined and used as a validation list for a column in another sheet.
What would the formula look like for this?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.Sign up to join this community
The best way I can think of to accomplish this is with importRange function.
Here's the general idea. You have three spreadsheet documents. Doc1, Doc2, Doc3.
Doc1: Range A1:A20 contains list of items you want to validate against Doc2: Range B1:B20 contains list of items you want to also validate against Doc3: Range C1:C20 contains range you wish to have validated against above lists
Note: I made the ranges distinct just for clarity - you can use A1:AXX or whatever you want for any of these ranges.
In Doc3, use the following formula to import the ranges from Doc1 A1:A20 and Doc2 B1:B20 using the following formula:
Place the following formula in Doc3 (let's say you put it in F1)
=importRange("[key= value on url for Doc1", "A1:A20")
Place the following formula in Doc2 (let's say you put it in G1)
=importRange("[key= value on url for Doc1", "B1:B20")
Now highlight range C1:C20 on Doc3. Right click and choose data validation.
Select for "criteria" as "items from a list"
Choose option "Create list from range"
For the range itself either enter Sheet1!F1:G20, or select it with the mouse.
Your range in Doc3 C1:C20 will now be validated against all the data elements from Doc1 and Doc2. If your list will be growing in size over time, make sure you make your range values large enough to accommodate that.