wiki:UserGuidelines/Importer/Excel

Version 7 (modified by Fran Boon, 7 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)
    

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.