I have a Google Sheet where I want to have column c in one of my sheets be a select drop down of employees based off of a different Google Spreadsheet.
I can go to row 2 of column c and enter:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xtTKXQr0QBaLHihCPeTgVhh3zy67emkCopDCu8SLdGk", "Sheet1!A2:A10")
Which just loads the whole column with the employees.
But this is not what I want, I want a dropdown. I was only testing the above because I couldn't get the same IMPORTRANGE to work with Data Validation.
If I instead go to C2 and then click "Data" and then "Data Validation" and I enter in that same URL that worked above:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xtTKXQr0QBaLHihCPeTgVhh3zy67emkCopDCu8SLdGk", "Sheet1!A2:A10")
then I can't save that validation as it says "Please enter a valid range".
Does anyone know why I would get an invalid range when I used the same IMPORTRANGE as described above and it worked (but didn't give me a dropdown). I already had to give permission for my one Google Sheet to be able to access the other Google Sheet.
I was not able to use IMPORTRANGE as a data validation as PatrickdC clarified.
I was able to do the following:
Create a Google Sheet (call it Sheet A) that has all employees in our organization.
From my other Sheet (call this Sheet B) I then did an IMPORTRANGE in the sheet cells rather than in data validation. This brings in all the data from my Sheet A. I then created another tabbed sheet on Sheet B and I was able to use the imported data on the other tab of Sheet B to then do an IMPORT for data validation.