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 vastly unaware of.

I've created drop down lists in the web app version of Microsoft Excel. My organization is using the most up-to-date version of the program. To do this, I've selected the cell I want to contain the drop down list in Sheet 1. In Sheet 2, I've created a table that contains the data I want in the drop down list. Then, I've gone to:

Data > Data Validation > Allow: List

I've then correctly selected the data as the \"Source\" from my table in Sheet 2. So far, so good. Problems arise, however, once I try and copy that cell containing the drop down list (cell A2 on Sheet 1) into another cell below it.

For instance, let's say that Sheet 1 contains a column for my marketing department's corporate communications initiatives entitled \"Type\". Under \"Type\", there are ten rows to allow for ten different types of communications that should have this information available for each one. These ten cells are cells A2-A12. Each of these cells should contain the same drop down list. For convenience, this drop down list is copied down cells A2-A12 so that I don't have to continuously set up a new drop down list each time. (I anticipate ~60 of these being needed for each month of 2023 - that's a lot of new drop down lists to create each time!)

The first, successful drop down list in cell A2 is set up to contain data from Table A in Sheet 2. Table A contains \"External\", \"Internal\" and \"Both\" as its data for the list. Cell A2 allows you to select from ALL of the three options -- this works because it's the original list that I set up, and it wasn't copied over from a different cell. Because I anticipate ~60 rows, I then copied this drop down list from A2 and pasted it into A3. The list in A3 only allows me to choose from \"Internal\" or \"Both\" now, and the first option, \"External\" is missing from the list. I go into the Data Validation screen again, and can see the range under \"Source\" is no longer the same range that was used to create the original list in A2. Instead, it only includes the two cells that contain \"Internal\" and \"Both\" as their contents from Table A in Sheet 2. The same is true for cell A4, but it just contains \"Both\" (the third and final option from Table A in Sheet 2), and cells A5-A12 are showing no options, as they are \"below\" the third and final option.

I \"do not have permission\" to upload images, so I cannot share screen shots unfortunately.

I hope I've explained this thoroughly. My question is: How can I prevent the range of data contained within my drop down list from changing when the drop down list is copied and moved into other cells?

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 vastly unaware of.

I've created drop down lists in the web app version of Microsoft Excel. My organization is using the most up-to-date version of the program. To do this, I've selected the cell I want to contain the drop down list in Sheet 1. In Sheet 2, I've created a table that contains the data I want in the drop down list. Then, I've gone to:

Data > Data Validation > Allow: List

I've then correctly selected the data as the \"Source\" from my table in Sheet 2. So far, so good. Problems arise, however, once I try and copy that cell containing the drop down list (cell A2 on Sheet 1) into another cell below it.

For instance, let's say that Sheet 1 contains a column for my marketing department's corporate communications initiatives entitled \"Type\". Under \"Type\", there are ten rows to allow for ten different types of communications that should have this information available for each one. These ten cells are cells A2-A12. Each of these cells should contain the same drop down list. For convenience, this drop down list is copied down cells A2-A12 so that I don't have to continuously set up a new drop down list each time. (I anticipate ~60 of these being needed for each month of 2023 - that's a lot of new drop down lists to create each time!)

The first, successful drop down list in cell A2 is set up to contain data from Table A in Sheet 2. Table A contains \"External\", \"Internal\" and \"Both\" as its data for the list. Cell A2 allows you to select from ALL of the three options -- this works because it's the original list that I set up, and it wasn't copied over from a different cell. Because I anticipate ~60 rows, I then copied this drop down list from A2 and pasted it into A3. The list in A3 only allows me to choose from \"Internal\" or \"Both\" now, and the first option, \"External\" is missing from the list. I go into the Data Validation screen again, and can see the range under \"Source\" is no longer the same range that was used to create the original list in A2. Instead, it only includes the two cells that contain \"Internal\" and \"Both\" as their contents from Table A in Sheet 2. The same is true for cell A4, but it just contains \"Both\" (the third and final option from Table A in Sheet 2), and cells A5-A12 are showing no options, as they are \"below\" the third and final option.

I \"do not have permission\" to upload images, so I cannot share screen shots unfortunately.

I hope I've explained this thoroughly. My question is: How can I prevent the range of data contained within my drop down list from changing when the drop down list is copied and moved into other cells?

DarbyNap 

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.