• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Data Validation to New Workbook

ShawnExcel

ShawnExcel

Member
I have two separate excel files, and I am trying to merge/combine them in to a single file. Both files have significant amounts of data validation, and they are not working when I do any of the following. It is worth noting that in File 1, there are two sheets: The Survey (with drop down menus & data validation) and the Database with all of the possible dropdowns for each question. I have tried:
  • Copy or move sheets from one workbook to another
  • Copy and paste special --> Validation
  • import the database sheet first, then the question sheet
The issue is, when File 1 is combined with File 2, the data validation is in place, but with the formula: ='[merge.xlsm]TechV Ref'!#REF!

Somehow I need to get rid of the file reference (merge.xlsm) and I need it to not lose the referenced cell and replace it with #REF!

Any ideas? Thank you in advance!
 
Hi ,

When you use the word combine , what exactly do you mean ?

If we designate the two files as WBK1 and WBK2 , you say WBK1 has 2 worksheets labelled The Survey and Database.

What happens in the following sequence of actions :

A:

1. Copy the tab Database to WBK2
2. Copy the tab The Survey to WBK2

B:

1. Move the tab Database to WBK2
2. Copy the tab The Survey to WBK2

C:

1. Copy the tab Database to WBK2
2. Move the tab The Survey to WBK2

D:

1. Move the tab Database to WBK2
2. Move the tab The Survey to WBK2

When you carry out any sequence of actions , see what happens when the first action is completed , before you carry out the second action in that sequence.

Narayan
 
When I say "Combine" I mean moving and copying like you suggest. Here are the results:

A) ='[merge.xlsm]TechV Ref'!#REF!
where merge.xlsm is WBK1
The original formula for that cell is: ='TechV Ref'!$B$212:$B$215

B) Similar results, but the result is: ='TechV Ref'!#REF! meaning we got rid of the reference to [merge.xlsm]

C) This is the result I get in the data validation box: ='[merge.xlsm]TechV Ref'!#REF!

D) ='TechV Ref'!#REF!

So the issue is why the actual cell references are turning in to #REF! ?

Thanks for the help
 
FYI when I move the database sheet from WKB1 to WKB2 but leave the survey, the Data validation formula changes to: ='[WKB2.xlsm]TechV Ref (2)'!#REF!
 
Hi ,

Everything is clear.

I tried out the entire procedure twice :

1. The DV dropdown list was an absolute direct range , as follows :

='TechV Ref'!$G$5:$G$11

When I moved the tab to another workbook , immediately the DV dropdown list showed the following reference :

='[Another workbook.xlsx]TechV Ref'!#REF!

The reference had got trashed.

2. The DV dropdown list was an absolute named range , as follows :

='TechV Ref'!DVList

When I moved the tab to another workbook , immediately the DV dropdown list showed the following reference :

=DVList

The Refers To box for DVList had changed from its earlier :

='TechV Ref'!$G$5:$G$11

to

='[Another workbook.xlsx]TechV Ref'!$G$5:$G$11

If all your DV lists are given range names , and your DV dropdowns refer to the named ranges , there will not be any problem even if you move the sheets to another workbook.

Narayan
 
You must log in or register to reply here.
Back
Top