need to copy/paste formula w/o it changing

[Bonnie]

Hi there! Using E02 on XP. Not a programmer but love
learning better ways to do stuff.

I have data being generated from an ooooold Lotus 1-2-3
file that can be pasted into Excel as values. There are 5
reports. Need about 100 cells from each for a big one page
summary report. (Someone has been typing it each month!)

So, I have created a file with the summary report on sheet
1 and the 5 Lotus reports are pasted to sheets 2-6. And
now I'm experiencing the joyous pain of typing a few
thousand formulas to link the cells. If I try to
copy/paste the formulas to speed things up, Excel keeps
using the wrong logic to 'change' the cell reference.
Problem is, my links on sheet 1 are linear and my data on
sheets 2-6 is running columnar. (Transposing the report
data is not an option.) So, what I want is:

=P4!R26 =P4!R27 =P4!R28
=P4!S26 =P4!S27 =P4!S28

When I try a copy/paste, I get something like this:

=P4!S27 =P4!T27 =P4!U27

Is there a way to paste a formula EXACTLY as you copied
it? Then I could just edit the letters as I go. Also, once
I get sheet2 linked to sheet1 is there a way to copy all
the links to sheets 3,4,etc.?

Just looking for a quick overview of tips and shortcuts to
best use my brain rather than frying it with monotonous
typing. Thanks in advance for any help or advice!!!

 

[Fredrik Wahlgren]

Hi there! Using E02 on XP. Not a programmer but love
learning better ways to do stuff.

I have data being generated from an ooooold Lotus 1-2-3
file that can be pasted into Excel as values. There are 5
reports. Need about 100 cells from each for a big one page
summary report. (Someone has been typing it each month!)

So, I have created a file with the summary report on sheet
1 and the 5 Lotus reports are pasted to sheets 2-6. And
now I'm experiencing the joyous pain of typing a few
thousand formulas to link the cells. If I try to
copy/paste the formulas to speed things up, Excel keeps
using the wrong logic to 'change' the cell reference.
Problem is, my links on sheet 1 are linear and my data on
sheets 2-6 is running columnar. (Transposing the report
data is not an option.) So, what I want is:

=P4!R26 =P4!R27 =P4!R28
=P4!S26 =P4!S27 =P4!S28

When I try a copy/paste, I get something like this:

=P4!S27 =P4!T27 =P4!U27

Is there a way to paste a formula EXACTLY as you copied
it? Then I could just edit the letters as I go. Also, once
I get sheet2 linked to sheet1 is there a way to copy all
the links to sheets 3,4,etc.?

Just looking for a quick overview of tips and shortcuts to
best use my brain rather than frying it with monotonous
typing. Thanks in advance for any help or advice!!!

Instead of =P4!R26, try =$P$4!$R$26

/Fredrik

 

[Bonnie]

Okaaaaaay, and the dollar signs do what? Why only wrap the
P and not the 4 on the sheet name? I'll play with it a
bit...Thanks for the bone. I'll take what I can get.

 

[Don S]

Hi there! Using E02 on XP. Not a programmer but love
learning better ways to do stuff.

I have data being generated from an ooooold Lotus 1-2-3
file that can be pasted into Excel as values. There are 5
reports. Need about 100 cells from each for a big one page
summary report. (Someone has been typing it each month!)

So, I have created a file with the summary report on sheet
1 and the 5 Lotus reports are pasted to sheets 2-6. And
now I'm experiencing the joyous pain of typing a few
thousand formulas to link the cells. If I try to
copy/paste the formulas to speed things up, Excel keeps
using the wrong logic to 'change' the cell reference.
Problem is, my links on sheet 1 are linear and my data on
sheets 2-6 is running columnar. (Transposing the report
data is not an option.) So, what I want is:

=P4!R26 =P4!R27 =P4!R28
=P4!S26 =P4!S27 =P4!S28

When I try a copy/paste, I get something like this:

=P4!S27 =P4!T27 =P4!U27

Is there a way to paste a formula EXACTLY as you copied
it? Then I could just edit the letters as I go. Also, once
I get sheet2 linked to sheet1 is there a way to copy all
the links to sheets 3,4,etc.?

Just looking for a quick overview of tips and shortcuts to
best use my brain rather than frying it with monotonous
typing. Thanks in advance for any help or advice!!!

This is going to be messy, but it works:

Select the cells with the formulas and perform a "find and replace"
finding "=" and replacing it with a " =" (add a space). You now have
a text string you can copy without changing cell references.

Copy and paste your selection to its new location.

Reverse the "find and replace" results replacing " =" with "="
(replace space= with =).

Franks answer works well too. Read up on absolute cell references to
see the power of the $ : )

Don S

 

[Bonnie]

Don, Don, Don! Thank you VERY much! You gave me exactly
what I need to go to my next nightmare; linking in 4 more
sheets and only the worksheet name will change so
your 'messy' fix is absolutely just what I needed. Frank's
answer gave me what I needed to finish that first sheet of
formulas and yup, those dollar signs lock it down.

Thanks again for taking the time to reply!!!