Copy Defined Names

[MBWoessner]

I have created a workbook with two sheets. The second sheet references the first sheet by using defined names. I want to send only the second sheet to clients to fill in information of their own and then copy the completed sheet back into the original workbook. I want to repeat this until I have a single instance of the first sheet (the master) and multiple versions (10+) of the second sheet all in one workbook. All of the second sheet copies should refer back to the first sheet by using the defined names.

The problem occurs when I try to copy the clients' completed sheet back into the original workbook. The copy works and the defined names are there, but when I look at the list of defined names for each of the copied second sheets, the defined name references have '#REF!' instead of 'MASTER!' like it should be.

I'm looking for a solution to this problem. Two thoughts that I had was to either copy the list of defined names from one sheet to the other or do a replace all in the corrupt defined names list and replace the '#REF!' with 'MASTER!'. However, I have no idea how to do either of those things.

Any help on this issue would be very appreciated. Thanks.

 

[MBWoessner]

Ok, I think I solved my own problem. I will try to explain my solution as best I can.

First of all, the real problem started when I sent only the second sheet to the clients. By deleting the first sheet, I created a new workbook with only one sheet (the second one). That made me lose all my defined name references, hence the #REF for every one of them. To fix this problem, instead of creating the new workbook by deleting the first sheet, I hid the first sheet and then protected the workbook so that there was no way for the clients to unhide it or even know that it was there. This allowed me to send out only the second sheet to the clients while still holding those defined name references.

The second problem came up when I tried to copy the clients' completed sheets back into the original workbook with the first sheet. The problem was that when you copy or move a sheet from one workbook to another, it still holds its reference to the other workbook. To fix that, after you copy or move the sheet to the other workbook, click on the 'Edit' menu and then select 'Links'. Once you are in the links window, it will show you that this sheet is referencing another workbook. Click the 'Change Source' button. Now select the workbook that you want it to reference (in my case, the workbook with the first sheet and all the other clients' second sheets). The Links window should now be empty meaning that there are no references to other workbooks. Click 'Close'.

Now all of the second sheet copies will reference the defined names on the first sheet. Problem Solved!

 

[xenou]

Just a warning: If the information on the "hidden" sheet is confidential, it is really not safe as a hidden sheet. Excel is not really a very secure application, so the protection will not be an obstacle if a user is motivated to get through it.