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.

mathetes's avatar
mathetes
Silver Contributor
Dec 11, 2019

Please,  Sameer_Kuppanath_Sultan, attach a sample spreadsheet if you can do so without revealing any confidential information. It's a bit difficult to visualize what you're talking about from your brief description.

  • Sameer_Kuppanath_Sultan's avatar
    Sameer_Kuppanath_Sultan
    Brass Contributor
    Dec 12, 2019

    mathetes Whenever I add an item in Table A, (it may repeat) if it’s a new item it should list in Table B

    I don’t want to use Pivot Table, since I have to place this list in another big calculation sheet.

     

    Please see the attached sheet for your ref. 

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Dec 12, 2019

      Sameer_Kuppanath_Sultan There is no reason why you wouldn't just place the pivottable on a separate worksheet.

      • Sameer_Kuppanath_Sultan's avatar
        Sameer_Kuppanath_Sultan
        Brass Contributor
        Dec 12, 2019

        Because, i need a variety of report in a same table with this list,

        any way I got a answer for this. Thank youJKPieterse 

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Dec 12, 2019

      Sameer_Kuppanath_Sultan 

      Try this in E5:

       

      =UNIQUE(dataentry[Department])

       

      Make sure that you clear all cells below. 

       

      NOTE: UNIQUE is a "Dynamic Array Function" (Beta) and available only through the Office Insiders program.

Resources