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

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 (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)
  

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?

  =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

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
  

Dropdown or manual selection

• ​ComboBox

Examples

• Volunteers for Malagasy Red Cross

Using Python

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

• ​XLWings
  • Can deploy standalone folders

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

• BluePrint for Excel Importer
• DeveloperGuidelines for Imports
• ​https://www.ablebits.com/office-addins-blog/2014/09/24/excel-drop-down-list/
• ​https://www.ablebits.com/office-addins-blog/2014/09/30/dependent-cascading-dropdown-lists-excel/
• ​http://www.excelcampus.com/tables/dependent-drop-lists/
• ​Popup Date Picker
• ​VBA Code Cleaner (32-bit Excel only unfortunately)