wiki:UserGuidelines/Importer/Excel

Version 9 (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

=L1s

Prevent modifying the L1 again if the L2 has been selected

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

Simple L2s based on L1

  • NB Dependent Lists must be sorted by dependency
    =OFFSET(L2_Start,MATCH(L1_Selected,L1s_for_L2,0),0,COUNTIF(L1s_for_L2,L1_Selected),1)
    

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 L2

  • ToDo: Multi-level lookups (Where L2s not Unique!)
  • @ToDo: Can we prevent changes if any Fokontany selected?
    =OFFSET(L3_Start,MATCH(L2_Selected,L2s_for_L3,0),0,COUNTIF(L2s_for_L3,L2_Selected),1)
    

Simple L4s based on L3

  • @ToDo: Multi-level lookups (Where L2s not Unique!)
    =OFFSET(L4_Start,MATCH(L3_Selected,L3s_for_L4,0),0,COUNTIF(L3s_for_L4,L3_Selected),1)
    

Multi-Select Dropdowns

  • http://www.contextures.com/excel-data-validation-multiple.html
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Developed by Contextures Inc.
    ' www.contextures.com
    '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
      Application.Undo
      oldVal = Target.Value
      If oldVal = "" Or newVal = "" Then
        Target.Value = newVal
      Else
        Target.Value = oldVal _
          & strSep & newVal
      End If
    End If
    
    exitHandler:
      Application.EnableEvents = True
    End Sub
    

Examples

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.