Some questions on copy/paste in excel 97

[Adam Kroger]

Greetings gurus and thanks in advance:

1: when copying and pasting formulas, is it possible to specify the "step"
the formula references will take between cells? Example: A formula in A1
references data in cell C1, when I copy the fomula to A2, I want the
reference to go to C3 (instead of C2).

2: If you have 2 workbooks, each with multiple sheets in them (Call them
Book1 and Book2). Each workbook has fomulas that call references from
sheets other than theeir own within their respectice workbooks. Can you
combine the 2 seperate workbooks into 1 workbook without the formula
references remaining attached to the original files? Example of what
happened to my formula:

=IF(ISBLANK('C:\Documents and Settings\Adam\Desktop\Battletech\COMBAT
TRACKER -OLD\[BattleTech - Combat Tracker
v1.0.xls]ROUNDS'!N68),"",'C:\Documents and
Settings\Adam\Desktop\Battletech\COMBAT TRACKER -OLD\[BattleTech - Combat
Tracker v1.0.xls]ROUNDS'!N68)

 

[Max]

.. 1: when copying and pasting formulas, is it possible to specify the "step"
the formula references will take between cells? Example: A formula in A1
references data in cell C1, when I copy the fomula to A2, I want the
reference to go to C3 (instead of C2).

One way

Taking your example process ..

Put in the starting cell, say A1: =INDIRECT("C"&ROW(A1)*2-1)
Copy A1 down

This would return the same as:

In A1: =C1
In A2: =C3
In A3: =C5
etc

 

[Guest]

1) One way is to copy the formula to C3 and the cut-and-paste it from C3 to C2

2) I don't know exactly what you want to achieve, but here are some
alternatives: If you just want to get rid of the link to the original files
you could use the paste- special-values alternative. If you want to redirect
the link to your new file you could use the edit link change source option or
the find and replace option (assuming that your new file is structured in the
same way as the original ones)

Kind regards Marcus

 

[Adam Kroger]

Thank you Marcus and Max, your information was exactly what I needed to keep
me from having to manually edit almost 300 cells of formulas.

 

[Max]

You're welcome, Adam !
Thanks for the feedback ..