Version 4 (modified by 8 years ago) ( diff ) | ,
---|
Excel Importer
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.
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 {{ =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!) {{ =OFFSET(L3_Start,MATCH(L2_Selected,L2s_for_L3,0),0,COUNTIF(L2s_for_L3,L2_Selected),1) }} # @ToDo: Can we prevent changes if any Fokontany selected? # 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) }}
Dependent Lists must be sorted by dependency
Examples
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
Attachments (1)
- Volunteer Import Template (mg).xlsm (493.6 KB ) - added by 8 years ago.
Download all attachments as: .zip