Re: problem

Tech-Archive recommends: Speed Up your PC by fixing your registry

I think you have a couple of ways to try....

1. Clean up the old form on the work*** and just paste the new form over that
***--select all the cells and edit|copy followed by Edit|paste.

2. If your new form has formulas that refer to other sheets in the workbook,
you can change the formulas to strings, copy the work*** (not just the cells)
and then convert the strings back to formulas.

in the new work*** (in the updated workbook)
Edit|replace
what: = (equal sign)
with: $$$$$
replace all
Now, the formulas are just text.
delete the old work***, and move in the new work***

Now reverse the process:
edit|replace
what: $$$$$
with: =
replace all
The text strings are back to formulas.

If you have other sheets that refer to the new sheet, you'll want to "freeze"
their formulas, too.

3. Open the workbook to be fixed.
Save it as Temp1.xls
select the work*** to be replaced
edit|move or copy
move to a new workbook
Save that workbook as Temp2.xls
Close those workbooks.

Rename Temp1.xls to TempOld.xls
Rename Temp2.xls to Tempextract.xls

Open the workbook that contains the work*** that was updated.
Save it as Temp1 (in the same location)
select the replacement work***
edit|move or copy
move to a new workbook
Save that workbook as Temp2.xls
Close those workbooks.


Now Temp2.xls has links back to Temp1.xls.
Rename Temp1.xls to TempNew.xls
Rename TempOld.xls to Temp1.xls
Open Temp1.xls (a copy of the old workbook with that single *** missing)
Open Temp2.xls (the new work*** with links to Temp1.xls (woohoo!)
select the work*** to move to temp1.xls
edit|move or copy
move to Temp1.xls
Now all the links in that updated work*** are pointing at temp1.xls
Save that workbook as the new name.


I'd do this with copies of each workbooks--just in case. And I'd use a
dedicated folder with nothing else in it -- just in case!

The only trouble I've ever had with this kind of stuff is named ranges--and I'm
don't recall if local names became global or global names became local. But I
do recall a problem that I had to fix when I was done.

(I used this technique when one of the worksheets in a giant workbook had become
corrupted. It actually saved lots of time. (difficult to believe, huh? <vbg>))




Ross wrote:
>
> Hi
> Here is the problem: I have created a workbook for the agency where I
> work. It has a serious of sheets that have the various forms we have to use
> to do our job and I have distributed this to the other people and they use
> it to write their reports. But New York State has made some changes on a
> few of their forms, so I have to change just them. These forms (sheets)
> refer to other sheets in the workbook, but when I try pasting in the new
> sheet I have created, the links to the other sheets in the workbook want to
> refer back to the one I did the altering in -- the book I am working on.
> How do I create a *** that I can paste in that will refer to the other
> named sheets in that book -- the tab names are the same in all workbooks
> that employees have.
> Ross

--

Dave Peterson
.

Quantcast