Copy/paste cells w/formulas

  • Thread starter Thread starter noone
  • Start date Start date
N

noone

Hello all,
I have a cell that contains the following:
=Sheet1!N2

If I copy it and paste it somewhere below, it looks like this
in the new cell:
=Sheet1!N19

Is there ANY way besides manually editing them, to have it
make it =Sheet1!N3 ?

I just need it to increment the N number by 1, instead of whatever
row it's on.

Many thanks.
 
Try adding absolutes to your formula:

If you always want the formula to be =Sheet1!N3 type
=Sheet1!N$3 instead.

Eric
 
Noone,

If you just want to increment the row by 1 wherever you copy it, this will
work (but it's not exactly straight-forward)

Copy the cell to the row below, it will now say =Sheet1!N3
Cut the formula from thsi new cell and copy it to your true target.
 
Hi Bob, and noone/nowhere,
I think you have to copy it from the formula bar and then hit
the [X} on the formula bar in order for that to work, and is
very dependent on the next cell below being available
as a temporary work area to carry this out...

The following formula has been added to
http://www.mvps.org/dmcritchie/excel/paste.htm#modFormula
and should do what noone/nowhere wants in converting
=sheet14!A8 to =Sheet14!A9 and placing the new formula
into the clipboard.

Option Explicit
Sub CopyAddrRowPlus1()
'D.McRitchie, misc, 2003-08-01, paste.htm#modFormula
'convert =sheet14!A8 to =Sheet14!A9 into clipboard
Dim txt As String, frml As String, msgx As String
Dim newTxt As String
txt = ActiveCell.Formula
msgx = "malformed pattern reference"
newTxt = "'ERROR**"
If Left(txt, 1) <> "=" Then
msgx = "missing ""="" sign at beginning of formula,"
GoTo malformed
End If
On Error GoTo malformed
newTxt = Left(txt, InStr(1, txt, "!")) & _
Range(Mid(txt, 2)).Offset(1, 0).Address(0, 0)
GoTo done
malformed:
MsgBox msgx _
& Chr(10) & "expected something like =sheet14!A8" _
& Chr(10) & "instead found " & txt
done:
'see http://www.cpearson.com/excel/clipboar.htm
Dim x As New DataObject
x.SetText newTxt
x.PutInClipboard
End Sub

If not familiar with installing/using macros see Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You might want to assign a keyboard shortcut to the macro

I'd certainly be interested in WHY you want to do this, all I can think
of is some kind of Table of Contents with all references to one other
sheet. Also would like to know if this was
a viable solution and how you implemented it (i.e. what shortcut, or icon)
 
Back
Top