Excel 5.0c - copying a formula from one worksheet to another

Excel 5.0c - copying a formula from one worksheet to another

Post by Hays McLea » Fri, 02 May 1997 04:00:00



I have two Excel worksheet files: Test1.xls and test2.xls.  Both are
multi-sheet files.

I have a formula on test1.xls sheet1 that references sheet2.  I want to
copy the formula to test2.xls and have it references sheet2 on
test2.xls.

However, when I copy the formula to test2.xls, it retains the remote
reference, copying the formula with a link back to sheet2 of test1.xls.

How can I copy so that the references retain there original _relative_
positions in a new file?

--Hays McLean

 
 
 

Excel 5.0c - copying a formula from one worksheet to another

Post by henner jueng » Sat, 03 May 1997 04:00:00



>I have two Excel worksheet files: Test1.xls and test2.xls.  Both are
>multi-sheet files.
>I have a formula on test1.xls sheet1 that references sheet2.  I want to
>copy the formula to test2.xls and have it references sheet2 on
>test2.xls.
>However, when I copy the formula to test2.xls, it retains the remote
>reference, copying the formula with a link back to sheet2 of test1.xls.
>How can I copy so that the references retain there original _relative_
>positions in a new file?

Hi Hays,

if I understand your problem here my solution: Edit your formula make
it a text by entering a sign e.g. # in front of the = then copy the
text in the new position remove the #. Of course after that remove the
#  in the original formula too.

--
HTH
Regards
Henner Juengst

 
 
 

Excel 5.0c - copying a formula from one worksheet to another

Post by Hays McLea » Sat, 03 May 1997 04:00:00




> >I have two Excel worksheet files: Test1.xls and test2.xls.  Both are
> >multi-sheet files.

> >I have a formula on test1.xls sheet1 that references sheet2.  I want to
> >copy the formula to test2.xls and have it references sheet2 on
> >test2.xls.

> >However, when I copy the formula to test2.xls, it retains the remote
> >reference, copying the formula with a link back to sheet2 of test1.xls.

> >How can I copy so that the references retain there original _relative_
> >positions in a new file?

> Hi Hays,

> if I understand your problem here my solution: Edit your formula make
> it a text by entering a sign e.g. # in front of the = then copy the
> text in the new position remove the #. Of course after that remove the
> #  in the original formula too.

> --
> HTH
> Regards
> Henner Juengst

Henner,

Thanks for your response.  Yes, your solution would work, I'm sure.
But, in my practical application, I have a spreadsheet full of these
types of formulas and it is impractical to do that to the whole
worksheet.

I think that I could also do an Edit-Replace and replace all references
to the first file in the new formulas as a work around.  I'm just hoping
that Excel has another and easier method to do this relatively common
(for me) application.  In Lotus, there is no such problem, the Copy
copies the formulas with their _relative_ references, unless you tell it
differently by making references _absolute_.

--Hays McLean

 
 
 

Excel 5.0c - copying a formula from one worksheet to another

Post by Robert Stonehou » Sun, 04 May 1997 04:00:00



>I have two Excel worksheet files: Test1.xls and test2.xls.  Both are
>multi-sheet files.
>I have a formula on test1.xls sheet1 that references sheet2.  I want to
>copy the formula to test2.xls and have it references sheet2 on
>test2.xls.
>However, when I copy the formula to test2.xls, it retains the remote
>reference, copying the formula with a link back to sheet2 of test1.xls.
>How can I copy so that the references retain there original _relative_
>positions in a new file?

Try selecting the formula in the formula bar and copying from there.
You will get the same characters exactly when you paste.

 
 
 

Excel 5.0c - copying a formula from one worksheet to another

Post by Torsten J. Schrim » Sun, 04 May 1997 04:00:00


Hi Hays McLean

You wrote at Fri, 02 May 1997 10:10:07 -0500:

Quote:>But, in my practical application, I have a spreadsheet full of these
>types of formulas and it is impractical to do that to the whole
>worksheet.
>I think that I could also do an Edit-Replace and replace all references
>to the first file in the new formulas as a work around.  I'm just hoping
>that Excel has another and easier method to do this relatively common
>(for me) application.  In Lotus, there is no such problem, the Copy
>copies the formulas with their _relative_ references, unless you tell it
>differently by making references _absolute_.

HAve a look in the edit-menu, last item. It should be "links" or
something like that (I have the German version, sorry) There you can
change the referenced file very easily.

Regards

Torsten
______________________________________________________________________
             Torsten J. Schrimpf - Duesseldorf - Germany

 
 
 

Excel 5.0c - copying a formula from one worksheet to another

Post by Russell Schu » Tue, 06 May 1997 04:00:00



>> >How can I copy so that the references retain there original _relative_
>> >positions in a new file?

>> if I understand your problem here my solution: Edit your formula make
>> it a text by entering a sign e.g. # in front of the = then copy the
>> text in the new position remove the #. Of course after that remove the
>> #  in the original formula too.

> Thanks for your response.  Yes, your solution would work, I'm sure.
> But, in my practical application, I have a spreadsheet full of these
> types of formulas and it is impractical to do that to the whole
> worksheet.

so do a global search-and-replace with '=' <=> '#'.  (make sure you
don't have any `#' characters that you want to keep, though).
--

 
 
 

Excel 5.0c - copying a formula from one worksheet to another

Post by Hays McLea » Tue, 06 May 1997 04:00:00



> Hi Hays McLean

> You wrote at Fri, 02 May 1997 10:10:07 -0500:
> >But, in my practical application, I have a spreadsheet full of these
> >types of formulas and it is impractical to do that to the whole
> >worksheet.

> >I think that I could also do an Edit-Replace and replace all references
> >to the first file in the new formulas as a work around.  I'm just hoping
> >that Excel has another and easier method to do this relatively common
> >(for me) application.  In Lotus, there is no such problem, the Copy
> >copies the formulas with their _relative_ references, unless you tell it
> >differently by making references _absolute_.

> HAve a look in the edit-menu, last item. It should be "links" or
> something like that (I have the German version, sorry) There you can
> change the referenced file very easily.

> Regards

> Torsten
> ______________________________________________________________________
>              Torsten J. Schrimpf - Duesseldorf - Germany

Torsten,

You are right!  Thanks.

--Hays

 
 
 

1. Copy multiples tables from one worksheet to separate worksheets

I have an Excel worksheet containing many tables of data. Each table is
separated by a blank row followed by a table title (which is merged across
all 6 columns). (If it's helpful I can make these titles all start with a
common word, like "Table," so "Table 1," "Table 2," etc.)

The tables all have 6 columns of data but the number of rows vary per
table. The first row contains column headings.

Is there a way to copy and paste each table (along with its title from the
merged row above it) to its own worksheet? Naming the worksheets based on
the the start of the title ("Table 1", "Table 2",) would be nice, too. I've
searched around and can't seem to find anything that addresses this.

Thank you.

2. Have a question about Refelection...

3. how to copy a formula without copy the formula......

4. A question about named ?

5. Copying formulas with worksheet ref.

6. State of Y2K, First of September

7. writing to a different worksheet in an excel file than the first one

8. SALE, Amiga RAM

9. Excel 5.0c; valid formula rejected by VBA

10. Selecting & Copying Data from One Worksheet to Another

11. Copying one worksheet to another - EXACTLY

12. Moving Excel 7.0 formulas among worksheets

13. Want to display all formulae of a Excel worksheet