|Version 10 (modified by 4 years ago) ( diff ),|
Table of Contents
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.
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
=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?
Simple L4s based on L3
- @ToDo: Multi-level lookups (Where L2s not Unique!)
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
It is possible to use Python to develop in Excel, e.g.
- 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
- BluePrint for Excel Importer
- DeveloperGuidelines for Imports
- Popup Date Picker