Forum Discussion

Sameer_Kuppanath_Sultan's avatar
Sameer_Kuppanath_Sultan
Brass Contributor
Dec 11, 2019
Solved

List of Items from Data Entry Form

Hi   I have a data entry sheet in the attached excel, i need to get list of department in another column whenever I enter department details in the data entry form automatically (by using formulas)...
Excel for web
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 12, 2019

    Sameer_Kuppanath_Sultan 

    For such layout

    in E5 you may use

    =IFERROR(INDEX(dataentry[Department],MATCH(0,INDEX(COUNTIF($E$4:$E4,dataentry[Department]),0),0)),"")

    and drag it down till the end of future range. With every new department in A it will appear in B.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 12, 2019

Sameer_Kuppanath_Sultan 

For such layout

in E5 you may use

=IFERROR(INDEX(dataentry[Department],MATCH(0,INDEX(COUNTIF($E$4:$E4,dataentry[Department]),0),0)),"")

and drag it down till the end of future range. With every new department in A it will appear in B.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 16, 2019

    Hi Sameer_Kuppanath_Sultan 

    For

    that could be for Table B

    =IFERROR(INDEX(dataentry[Department],MATCH(0,IF(dataentry[Condition]="Regular", 0, 1)+ COUNTIF($F$4:$F4,dataentry[Department]),0),),"")

    and for the Table C

    =IFERROR(INDEX(dataentry[Department],MATCH(0,IF(dataentry[Condition]="Non Regular", 0, 1)+ COUNTIF($F$15:$F15,dataentry[Department]),0),),"")

    Above are array formulas (Ctrl+Shift+Enter)

Resources