Greetings! I am currently fixing up a spreadsheet which contains work experience placements for my organisation. My supervisor would like a column for "List of Duties" for each placement, however just typing out the information would make the rows/columns very large. So we came up with the idea of putting the duties in a drop down menu simply for the sake of taking up less space.

I have created a new, separate sheet to input the duties and then use these cells as the source for the list via Data Validation. The only problem is, when I drag the cell down, the source does not auto increment, giving all the work placements the list of duties from the first one.

is the source range for the first placement. What I want is for it to change to
for the next row and so on, but I can't seem to make excel do this automatically. Is there any way to do this without manually selecting the range for each individual list?

Sorry if this isn't explained clearly, I'm very new to Excel and don't really know the proper lingo.