How do I stop a data validation formula changing when I copy and paste it?

New Contributor


I have a workbook with two worksheets. In worksheet 1 I have set up a drop-down list using data validation from the list I have created in worksheet 2.  


The formula for the validation looks like this =Sheet1!$A2:A90 


The problem I am having is in worksheet 1 when I copy this formula into the next cell down the formula changes.


For example, in cell C2 the drop-down list pulls the data from the data validation using this formula =Sheet1!$A2:A90 but when I copy and paste the drop list into cell C3 the formula changes to =Sheet1!$A3:A91

2 Replies
best response confirmed by Hans Vogelaar (MVP)

@JessColusso You need to make all column and row references absolute, so use $A$2:$A$90 in stead.