Solved: When replacing a sheet, how to fix refs?

[Gingertrees]

Hello,
Background: I maintain a workbook used by around 30 people in which they place their client's info on the Cases sheet and the several other sheets all reference the Cases sheet. (Via a drop-down ID at the top of each sheet called a URN.) Each user has 1 copy of the workbook.

The other sheets need so many updates, I think it would be faster to just give all my users a new copy of the workbook, and do a "move or copy sheet" for their existing Cases sheet into the new workbook. This creates a few problems: named cells/ranges have to be fixed and added again (doable), and the Data Val on the URN on the other sheets get messed up (not doable). Problem is, I can't seem to fix this latter problem. Even removing Data Validation altogether, redefining the named range, and redoing it, it errors.

Help?

More hints:
--> the URN drop down references the 1st column of Cases in a named range called NAMES
--> most other data in the other sheets uses VLOOKUP referencing all the data on Cases in a named range called CLIENTS

[CHatmaker]

You appear to want to have a database front ended by XL. I would like to help as a test case for a book I'm considering.

The advantages are:

• You can keep your XL forms
• Your users will only use XL
• You can keep the data in: MS Excel, MS Access, MS SQL Server, Oracle, IBM DB2 AS/400 (i/series), or any other database
• The cases will be available for other reports created in XL (or Crystal Reports)
• There is nothing to buy or install
• Your VBA skills are all that is needed

Would this work for you?

[rbrhodes]

Hi GT,

Not sure but:

I opened the workbook, deleted the 'Cases' sheet and did a save as - simulating a new workbook without the Cases sheet.

I then opened the original (with the Case sheet intact) and did a move\Copy sheet with 'make a copy' checked and copied sheet 'Cases' to the WB without it. Worked.

[Gingertrees]

Oops. By deleting the defined name "Names" prior to pasting, I fixed the paradox.