PDA

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



Gingertrees
08-17-2009, 10:04 AM
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.:dunno

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
08-17-2009, 12:32 PM
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 neededWould this work for you?

rbrhodes
08-17-2009, 12:37 PM
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
08-17-2009, 12:59 PM
Oops. By deleting the defined name "Names" prior to pasting, I fixed the paradox.