wiki:UserGuidelines/Importer/Excel

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:

=ISNUMBER(MATCH("*@*.?*",A2,0))

Phone Number (10 or 11 digits, however loses leading zero as can't then format as text):

=AND(ISNUMBER(A2),LEN(A2)>9,LEN(A2)<12)

Lookup Lists

Simple L1s

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

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 (make sure that the row has no $ in front of it)
  • L1s_for_L2 is the list of L1s to the left of the list of associated L2s
    =OFFSET(L2_Start,MATCH(L1_Selected,L1s_for_L2,0),0,COUNTIF(L1s_for_L2,L1_Selected),1)
    

Simple L3s based on a single L2

  • ToDo: Multi-level lookups (Where L2s not Unique!)
    =OFFSET(L3_Start,MATCH(L2_Selected,L2s_for_L3,0),0,COUNTIF(L2s_for_L3,L2_Selected),1)
    

Simple L4s based on a single 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)
    

Simple L4s based on a varying L3 in row

  • same as above, set L3_Selected = UI$C6

To clear dependent entries when a Higher Lx is changed then can use the technique outlined here (although needs modification as we're not using tables):

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

Last modified 4 months ago Last modified on 05/17/21 19:30:11

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.