wiki:UserGuidelines/Importer/Excel

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

See Also

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.