Forum Discussion
Auto fill Department column
Good afternoon Microsoft Community,
This is my first post ever with this site. I have the following project that I need help with.
I need to auto fill the Department in column A in the Raw Data worksheet, with the DEPARTMENT name on worksheet Supervisor Drop-Down list.
The Raw Data worksheet has Column F with the names of the current employees. I want a function/formula that will check for the names in the Supervisor's Drop-Down list and automatically add that department to Column A on the Raw Data worksheet.
I have explore many different videos on the web and have not been able to find a solution.
please advise on the best approach to handle this task.
Thank you. On the RawData sheet, the names are Employee 1, Employee 2 etc., but on the SupervisorsDropdown, they are Employee Name 1, Employee Name 2, etc.
The formula didn't take this discrepancy into account. I changed the names on the RawData sheet, and now it works:
(I modified the formula to return an empty string if there is no match).
See the attached version.
In A2:
=INDEX('Supervisor's Drop-Down'!$A$1:$F$1, MAX(('Supervisor's Drop-Down'!$A$2:$F$51=F2)*COLUMN('Supervisor's Drop-Down'!$A$2:$F$51)))
Fill down.
Thank you HansVogelarr for your amazing quick response. Unfortunately I got the following error after entering the function
I appreciate you for providing me with a possible solution. I'll keep trying you recommendations. Thanks.
Sorry, I should have seen that - you have to double the apostrophe in the sheet name:
=INDEX('Supervisor''s Drop-Down'!$A$1:$F$1, MAX(('Supervisor''s Drop-Down'!$A$2:$F$51=F2)*COLUMN('Supervisor''s Drop-Down'!$A$2:$F$51)))
