How to keep Data Validation list after a drag and drop...

**pasqualebaldi** · 02-05-2015, 08:40 PM

Is there a way to keep a column in a table to have a data validation drop-down list after you drag and drop a cell into another cell?

For example:
A1 has a data validation list. If I drag A1 to A5, A1 no longer has the data validation. I want all cells in column "A" in my table to always have the same data validation list even after a drag and drop.. Is this possible?

Thank you

**MarvinP** · 02-05-2015, 08:48 PM

Hi pasquale and welcome to the forum,

This sounds like job for Dynamic Named Ranges. I'd need to see your data to be sure. See DNR's at:
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.onlinepclearning.com/exce...-named-ranges/

**pasqualebaldi** · 02-05-2015, 09:09 PM

Originally Posted by MarvinP

Hi pasquale and welcome to the forum,

This sounds like job for Dynamic Named Ranges. I'd need to see your data to be sure. See DNR's at:
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.onlinepclearning.com/exce...-named-ranges/

Here is and example of the problem I am trying to fix:
Data Validation Problem.xlsx

**FlameRetired** · 02-05-2015, 09:49 PM

When you say drag and drop it sounds like you're moving those cells. Try holding Ctrl while dragging and dropping and see if that helps. It should make copies of A1 or A2:C3 as your workbook indicates.

**MarvinP** · 02-05-2015, 10:18 PM

Hi pasquale,

It looks like "drag and drop" isn't what you want to do. How about Copy and Paste might be what is appropriate for what you are doing. To Copy a range, select it and press Ctrl+C. Then click on a cell (empty) and press Ctrl+V.

I think that is what you are trying to perform but dragging and dropping isn't what you want to do.

**pasqualebaldi** · 02-09-2015, 02:14 PM

Originally Posted by MarvinP

Hi pasquale,

It looks like "drag and drop" isn't what you want to do. How about Copy and Paste might be what is appropriate for what you are doing. To Copy a range, select it and press Ctrl+C. Then click on a cell (empty) and press Ctrl+V.

I think that is what you are trying to perform but dragging and dropping isn't what you want to do.

Thank you for the reply, but the reason I would like to drag and drop is because there are times I want to move the information from my table down so I can put different information above it, but I still need the data validation for the new information. And it becomes tedious reapplying the data validation list in the cells.

If there is no way of doing this, I can copy and paste and then delete the original information so I can put the new info in. I am just trying to make the process as quick as possible with less steps.

Thank you

**MarvinP** · 02-09-2015, 03:31 PM

Hi,

I don't think you got the Dynamic Named Range (DNR) answer I gave before. If you add a word to the bottom of Table1 or Table2 it will be included in DNRTable1 or DNRTable2 range. These are DYNAMIC!! Then I used these names in the Data Validation List Formula. See the attached. Look at the Validation List formula and Names Manager to see what I've done.