SecureSheet uses a very specific technique (there are many) for dependent dropdowns to work in both SecureSheet and in Excel. In this technique, it is required that groupings for dependent selection must be grouped across contiguous rows for the Excel lookup to work correctly.


For instructions and an example spreadsheet with a dependent drop down built following these steps, click here.


  1. Create a supporting tab to setup dropdown values for dependent data validation(s) (e.g., Data).
  2. Create a named range for your first dropdown.  In this example, the named range is DepartmentName (on the Data tab, Cells C7:C14).
  3. Create a named range for the second dropdown, which is dependent on the value selected from the first dropdown.  For example, after selecting a department (from the first dropdown), these would be the job titles (second dropdown) available for it.
  4. On the main tab, create a data validation for Department that links to the DepartmentName named range on your Data tab.
  5. For the dropdown that is dependent on the Department selected, create a data validation that refers to the ssDepList named range on your Data tab.  This data validation needs to have this formula (refer to the considerations section when you have more than one dependent dropdown - the dependent data validations formulas must match the naming convention):
    .=OFFSET(ssDepList, MATCH(B5,OFFSET(OFFSET(ssDepList,0,0,1,1),0,0,ROWS(ssDepList),1),0)-1, 1, COUNTIF(ssDepList,B5), 1)


Considerations

  • The Named Range(s) for dependent dropdowns must follow the naming convention ssDepList, ssDepList1, ssDepList2 .... through ssDepList20.  SecureSheet looks for this specific naming convention to treat these as dependent dropdowns.
  • The dependent dropdown data validation formula must match the naming convention. When you have more than one dependent dropdown, adjust the OFFSET ssDepList Named Range number and MATCH cells with the correct references. For example:
    .=OFFSET(ssDepList1, MATCH(B5,OFFSET(OFFSET(ssDepList1,0,0,1,1),0,0,ROWS(ssDepList1),1),0)-1, 1, COUNTIF(ssDepList1,C5), 1)
  • For the OFFSET to work properly, the lists on the data tab will need to remain grouped by the first column.  In this example, the Accounting jobs must be in contiguous rows, Business Development jobs in contiguous rows, etc.


Example of a main tab:


Example of a supporting tab (data):