Changes between Version 21 and Version 22 of UserGuidelines/Importer/Excel


Ignore:
Timestamp:
05/17/21 19:30:11 (4 years ago)
Author:
Fran Boon
Comment:

New, better method to clear dependent entries (old version no longer seems to work anyway)

Legend:

Unmodified
Added
Removed
Modified

  • UserGuidelines/Importer/Excel

    v21 v22  
    3333}}}
    3434
    35 Prevent modifying the L1 again if the L2 has been selected
    36 {{{
    37 =IF(L2_Selected="", L1s, INDIRECT("FakeList"))
    38 }}}
    39 
    4035Simple L2s based on a single L1
    4136* NB Dependent Lists must be sorted by dependency
    … …  
    4641=OFFSET(L2_Start,MATCH(L1_Selected,L1s_for_L2,0),0,COUNTIF(L1s_for_L2,L1_Selected),1)
    4742}}}
    48 With prevention
    49 {{{
    50 =IF(L3_Selected="", OFFSET(L2_Start,MATCH(L1_Selected,L1s_for_L2,0),0,COUNTIF(L1s_for_L2,L1_Selected),1), INDIRECT("FakeList"))
    51 }}}
    5243
    5344Simple L3s based on a single L2
    5445* !ToDo: Multi-level lookups (Where L2s not Unique!)
    55 * !ToDo: Can we prevent changes if any L4 selected?
    5646{{{
    5747=OFFSET(L3_Start,MATCH(L2_Selected,L2s_for_L3,0),0,COUNTIF(L2s_for_L3,L2_Selected),1)
    … …  
    6555
    6656Simple L4s based on a varying L3 in row
    67 - same as above, set L3_Selected = UI$$C6
     57- same as above, set L3_Selected = UI$C6
     58
     59To clear dependent entries when a Higher Lx is changed then can use the technique outlined here (although needs modification as we're not using tables):
     60 * https://www.contextures.com/exceldatavaldependindextablesindirect.html#clear
     61 * Full, modified, example: https://github.com/sahana/eden/raw/master/static/themes/CumbriaEAC/xls/Client_Registration.xlsm
    6862== Multi-Select Dropdowns ==
    6963* http://www.contextures.com/excel-data-validation-multiple.html