Form Dropdown from another sheet


I have a sheet with a table of employee names & job title for all users in the company

I have another sheet which logs the requests from these users.

So one sheet is a list of people and another is a log of items they request for.

I have a form to capture requests from the list of users to insert into the log of requests.

Since i cannot have a drop down of employee names as a column in my log sheet ( as per, SO I copy the list of names from the employee list sheet and insert it into the form field as a drop down for the user to select his name.

this then adds his name to the log using the exact text of his own name letting me match the two columns in different sheets for analytics etc.

So Currently when I add new employees to the employee list, I have to remember to add them to the drop down on the other sheet's form as well. maintaining two sets of lists is error prone and not ideal.

Please tell me there is a way I can update both at the same time or a smarter way of doing this seeing as we cant create a drop down list with values from a column in a different sheet.

p.s. please dont suggest I buy the premium "Data Uploader" to do what excel has been able to do for over a decade.

Kind Regards,



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The best I can suggest is to insert a new text/number column. In whatever row you want of that column you can use something along the lines of...

    =JOIN(DISTINCT([Name Column]:[Name Column]), CHAR(10))

    This will join a single entry of each name together into the same cell. The CHAR(10) delimiter is a line break.

    Next you can set up an alert to trigger whenever an row in that column changes. This means the alert will trigger any time a name is added, removed, or changed. You can use a placeholder in the Alert to go ahead and drop the list right there into your email.

    Since it is line break delimited, you can simply copy/paste directly into the dropdown column properties.

    I know this still involves manual entry for the column properties, but it is simply copy/paste, and you can an alert every time it changes so you don't have to worry about going in every day and checking if it needs updated or anything.

  • Hi Paul,

    Thanks but I am already doing manual copy paste from the source excel sheet so no point complicating my SS sheet if im not getting any closer to the required result.

    Thanks anyway.

    Kind Regards,