Spreadsheet design notes

Creating an Excel drop down from a SharePoint List

  1. Open the SharePoint List created previously (see How to Create a SharePoint List from Winshuttle Query).
  2. Under List Tools, click List, and then click Export to Excel.

  3. Save the Excel Web Query file (the file has an .iqy extension) to a common SharePoint file library that is accessible to all users.
  4. Open the Excel file you want to add the drop down list to.
  5. On the Data Tab, click Connections to Import the data connection file.

  6. In the Workbook Connections dialog box, click Add.
  7. In the Existing Connections dialog box, click Browse for More, and then select the file you saved from step 3.

    Add as many lists as you need for your Excel file.

  8. In the Workbook Connections dialog box, select a list, and then click Properties.
  9. Click Refresh, and then set the data refresh to a schedule, or when the file is opened.

    Return to Excel for the remainder of this process.

  10. In Excel, click the Data tab.
  11. Click Existing Connections.
  12. Select the data connection added in the previous section (above).
  13. Click Open.
  14. On the Import Data dialog box, do the following:

  15. Select Table
  16. Select where you want to put the data (New worksheet in this case).
  17. Click OK to download the data.

For the final part of this example, you will configure the drop down list in the spreadsheet so it is based upon the Excel table populated from the SharePoint List.

  1. Highlight the column or cells where you want the drop down to appear.
  2. Under the Data tab click on Data Validation button.
  3. In the Data Validation dialog box, on the Settings tab, do the following:
    • Allow: Select List.
    • Source: Set the source to the cells in the Table with the data you want for the drop down (i.e. the table in Excel where data is updated from the SharePoint list).

  4. Click OK
  5. In your Excel file, verify the dropdown options are present in the specified cells.