Version 12 (modified by Fran Boon, 4 years ago) ( diff )


Excel Importer

Excel can be used to build a more complex front-end for importable spreadsheets than is possible with just CSV.

The Excel importer currently will import a worksheet called 'SahanaData', or the first worksheet if none with the correct name exist.

Normally this is a hidden worksheet with a user-visible UI worksheet where data is entered & 1 or more lookup worksheets.

Lookup Lists

Simple L1s

  • define a Named Range
  • select the column
  • deselect the header
  • Data | Data Validation
  • List | Source:

Prevent modifying the L1 again if the L2 has been selected

=IF(L2_Selected="", L1s, INDIRECT("FakeList"))

Simple L2s based on a single L1

  • NB Dependent Lists must be sorted by dependency

With prevention

=IF(L3_Selected="", OFFSET(L2_Start,MATCH(L1_Selected,L1s_for_L2,0),0,COUNTIF(L1s_for_L2,L1_Selected),1), INDIRECT("FakeList"))

Simple L3s based on a single L2

  • ToDo: Multi-level lookups (Where L2s not Unique!)
  • ToDo: Can we prevent changes if any L4 selected?

Simple L4s based on a single L3

  • ToDo: Multi-level lookups (Where L2s not Unique!)

Multi-Select Dropdowns

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Developed by Contextures Inc.
    'code runs on protected sheet
    Dim oldVal As String
    Dim newVal As String
    Dim strSep As String
    Dim strType As Long
    'add comma and space between items
    strSep = ", "
    If Target.Count > 1 Then GoTo exitHandler
    'checks validation type of target cell
    'type 3 is a drop down list
    On Error Resume Next
    strType = Target.Validation.Type
    If Target.Column = 3 And strType = 3 Then
      Application.EnableEvents = False
      newVal = Target.Value
      oldVal = Target.Value
      If oldVal = "" Or newVal = "" Then
        Target.Value = newVal
        Target.Value = oldVal _
          & strSep & newVal
      End If
    End If
      Application.EnableEvents = True
    End Sub


Using Python

It is possible to use Python to develop in Excel, e.g.

However, is it worth the deployment complexity for the ease of coding?

NB Office 365 can be downloaded with a free month's trial to allow developing a template

See Also

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.