Forum Discussion
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)...
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.
A pivot table or a Powerquery query comes to mind.
PT: Insert a pivot table and drag the field from which you need unique items to the row area.
PQ: Data, From Table, Delete Rows drop-down, Remove Duplicates, Close & Load, Close & Load to, select location.
Both options require you to push "Refresh All" before the list is updated.
PT: Insert a pivot table and drag the field from which you need unique items to the row area.
PQ: Data, From Table, Delete Rows drop-down, Remove Duplicates, Close & Load, Close & Load to, select location.
Both options require you to push "Refresh All" before the list is updated.
PT: I have to place this list in another calculation sheet, where i cannot apply pivot table option.
so that, every time I have to copy the list from PT and paste it to my calculation field.
What I need is a formula for automatic "remove duplicates" whenever i enter data in other column or sheet. Can it be done??
PQ: can you explain with a working?
