Forum Discussion
"Source" data changes when cell containing drop down list is copied into a new cell
Good morning! I am hoping someone can assist with this. I have not been able to figure it out! I also want to note that I am not the most well-versed Excel user and there might be some concepts I am ...
Select A2, then click Data Validation on the Data tab of the ribbon.
You will probably see something like
=Sheet2!B2:B4
where B2:B4 is the range containing the options. The reference to B2:B4 is relative, i.e. Excel will change it dynamically when you copy the cell with the drop down to another location. To prevent this, make the reference absolute, i.e. fixed. The easiest way to do this is to select B2:B4 and then press the function key F4. This will change the above formula to
=Sheet2!$B$2:$B$4
The $ signs indicate that the reference is absolute.
Click OK to confirm the change, then copy the cell downwards.