Version 13 (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.

Data Validation

  • Data | Data Validation
  • Custom | Formula:

Email Address:


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
  • L2_Start is the column header on the lookups sheet
  • L1_Selected is the single field where L1 is selected
  • L1s_for_L2 is the list of L1s to the left of the list of associated L2s

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.