CODEWITHSUNDEEP
excelvba
yoshiserry
yoshiserry

Reputation: 21355

Copying data validation from one worksheet to another in an excel workbook

I've got three worksheets setup.

The first contains reference data for my data validation list.

The second references the first sheet for a data validation drop down list. Something like sheet1!$a1:$a3

The problem arises when I try and copy the cell in sheet two that contains the data validation drop down list to a cell in sheet 3 where the destination cell is empty.

I'm trying to copy excel data validation but when copied across to sheet 3 it references the cells $a1:$a3 but doesn't maintain the reference to sheet 1 where the data for the drop down list is actually stored.

How can I copy the data validation such that when it is copied to sheet 3 it still maintains the reference to sheet1 where the static data that makes up the list is.

Upvotes: 0

Views: 3980

Answers (1)

Dillon Engelmann
Dillon Engelmann

Reputation: 1

If I am understanding you correctly, the pasted Data Validation list is not showing the corrrect information. In my workbook I needed multiple cells of DV to display info from the sheet labled "Parts" after they were copied to the sheet labled "Ascending".

What I had to do was select my newly, and thus far incorrectly, pasted material, go to the 'Data' tab and click into 'Data Validaion --> Data Validation' (drop down menu). From here I had to edit the source to reflect the origional sheet.

Was: =$C$4:$C$21

Edit: =Parts!$C$4:$C$21

This determines what sheet the data is being validated from. Once Ive done this, I can copy and paste a DV interal to whatever sheet I am working on.

For some reason naming the range from the origional sheet (Parts) did not carry over in the pasted DV. VLOOKUP and other formulas thankfully work fine.

Upvotes: 0

Related Questions