Supress col/row increment with copy

Question with (hopefully) an easy answer....

When you copy a cell that uses a formula referring to another cell,
for simple illustration say "=C4"  it will increment based on where
you paste it.  So, it will increment up to "=C5" if you paste it in
the next cell, or "=D4" if you paste below.

How can I suppress that, so that when I paste the new cell receives
"=C4" as well.

(I know I can simply copy the text and paste that in, but I want to
copy a whole row of formulas to be the same.  I thought
Paste>Special>Formulas would work, but it seems to be the same as
regular paste)

Thnx in advance

Ross.
0
11/24/2003 3:28:48 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
289 Views

Similar Articles

[PageSpeed] 57

Use absolute references

=$C$4

you can select the cell in question, press F2 and then press F4
and it will change. Or you can run a macro like this

Sub RepCellAbs()

Dim formula As String
Dim cell As Object

For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)

    formula = cell.formula
    cell.formula = Application.ConvertFormula(formula, _
    fromreferencestyle:=xlA1, _
    toreferencestyle:=xlA1, toabsolute:=xlAbsolute)

Next

End Sub


-- 

Regards,

Peo Sjoblom


"scrodchunk" <scrodchunk@hotmail.com> wrote in message
news:90f0e62d.0311240728.7b3b97d5@posting.google.com...
> Question with (hopefully) an easy answer....
>
> When you copy a cell that uses a formula referring to another cell,
> for simple illustration say "=C4"  it will increment based on where
> you paste it.  So, it will increment up to "=C5" if you paste it in
> the next cell, or "=D4" if you paste below.
>
> How can I suppress that, so that when I paste the new cell receives
> "=C4" as well.
>
> (I know I can simply copy the text and paste that in, but I want to
> copy a whole row of formulas to be the same.  I thought
> Paste>Special>Formulas would work, but it seems to be the same as
> regular paste)
>
> Thnx in advance
>
> Ross.


0
terre08 (1112)
11/24/2003 3:35:42 PM
Thanks for the replies about absolute references - I should have
mentioned that the column I'm copying is one that needs to be created
with copies as well, so hvaing absolute references there will impede
me from copying and creating the source cells in the first place.

Using F4 to change each cell in the column to absolute before copying
is one approach I guess - but still labour intensive.

Ideally I'd like to set up my references in one cell, copy and paste
to create appropriate formulas in the next (with increments happening)
then copy that whole column and <ALT><SHIFT><LIFT LEFT LEG> Paste to
get that column to make my new column have the same formulas (without
increments).  Sounds like a macro is the only way to go.

Thx.
Ross.




"Jason Morin" <jason.morin@us.exel.com> wrote in message news:<50f601c3b2a1$42641580$a601280a@phx.gbl>...
> You have to turn the cell reference into an absolute 
> reference by adding $'s. The quick way is to click inside 
> the cell reference (or if a range, select the range) while 
> in the formula bar and press F4.
> 
> =$C$4 (absolute)
> =$C4 (mixed - column is frozen)
> =C$4 (mixed - row is frozen)
> =C4 (relative)
> 
> 
> HTH
> Jason
> Atlanta, GA
> 
> >-----Original Message-----
> >Question with (hopefully) an easy answer....
> >
> >When you copy a cell that uses a formula referring to 
>  another cell,
> >for simple illustration say "=C4"  it will increment 
>  based on where
> >you paste it.  So, it will increment up to "=C5" if you 
>  paste it in
> >the next cell, or "=D4" if you paste below.
> >
> >How can I suppress that, so that when I paste the new 
>  cell receives
> >"=C4" as well.
> >
> >(I know I can simply copy the text and paste that in, but 
>  I want to
> >copy a whole row of formulas to be the same.  I thought
> >Paste>Special>Formulas would work, but it seems to be the 
>  same as
> >regular paste)
> >
> >Thnx in advance
> >
> >Ross.
> >.
> >
0
11/24/2003 7:47:06 PM
Ross

Create your first column using relative addressing so's you can increment.

Then select that column and run this macro to change to absolute.

Sub Absolute()
    Dim cell As Range
    For Each cell In Selection
    If cell.HasFormula Then
       cell.Formula = Application.ConvertFormula _
       (cell.Formula, xlA1, xlA1, xlAbsolute)
    End If
    Next
End Sub

Now you can copy this column to another.

OR just use the following to copy the formulas as is to the other column.

Cannot remember who first posted this code.  Maybe Tom Ogilvy or John
Walkenbach but.......???

Sub CopyFormulasExact()
    Dim rngCopyFrom As Range
    Dim rngCopyTo As Range
    Dim intColCount As Integer
    Dim intRowCount As Integer

    '   Check that a range is selected
    If Not TypeName(Selection) = "Range" Then End
    '   check that the range has only one area
    If Not Selection.Areas.Count = 1 Then
        MsgBox "Multiple Selections Not Allowed", vbExclamation
        End
    End If

    '   Assign selection to object variable
    Set rngCopyFrom = Selection
    If Not Selection.HasFormula Then
        MsgBox "Cells do not contain formulas"
        End
    End If

    '   This is required in case cancel is clicked.
    '   Type 8 input box returns a range object if OK is
    '   clicked or False if cancel is clicked.  I do not
    '   know of a way to test for both cases without
    '   using error trapping
    On Error GoTo UserCancelled

    '   Assign object variable to user-selected cell
    Set rngCopyTo = Application.InputBox( _
            Prompt:="Select the UPPER LEFT CELL of the " _
            & "range to which you wish to paste", _
            Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

    On Error GoTo 0

    '   Loop through source range assigning any formulae found
    '   to the equivalent cell of the destination range.
    For intColCount = 1 To rngCopyFrom.Columns.Count
        For intRowCount = 1 To rngCopyFrom.Rows.Count
            If rngCopyFrom.Cells(intRowCount, _
                    intColCount).HasFormula Then
                rngCopyTo.Offset(intRowCount - 1, _
                        intColCount - 1).Formula = _
                        rngCopyFrom.Cells(intRowCount, _
                        intColCount).Formula
            End If
        Next intRowCount
    Next intColCount

UserCancelled:
End Sub

Gord Dibben   XL2002


On 24 Nov 2003 11:47:06 -0800, scrodchunk@hotmail.com (scrodchunk) wrote:

>Thanks for the replies about absolute references - I should have
>mentioned that the column I'm copying is one that needs to be created
>with copies as well, so hvaing absolute references there will impede
>me from copying and creating the source cells in the first place.
>
>Using F4 to change each cell in the column to absolute before copying
>is one approach I guess - but still labour intensive.
>
>Ideally I'd like to set up my references in one cell, copy and paste
>to create appropriate formulas in the next (with increments happening)
>then copy that whole column and <ALT><SHIFT><LIFT LEFT LEG> Paste to
>get that column to make my new column have the same formulas (without
>increments).  Sounds like a macro is the only way to go.
>
>Thx.
>Ross.
>
>
>
>
>"Jason Morin" <jason.morin@us.exel.com> wrote in message news:<50f601c3b2a1$42641580$a601280a@phx.gbl>...
>> You have to turn the cell reference into an absolute 
>> reference by adding $'s. The quick way is to click inside 
>> the cell reference (or if a range, select the range) while 
>> in the formula bar and press F4.
>> 
>> =$C$4 (absolute)
>> =$C4 (mixed - column is frozen)
>> =C$4 (mixed - row is frozen)
>> =C4 (relative)
>> 
>> 
>> HTH
>> Jason
>> Atlanta, GA
>> 
>> >-----Original Message-----
>> >Question with (hopefully) an easy answer....
>> >
>> >When you copy a cell that uses a formula referring to 
>>  another cell,
>> >for simple illustration say "=C4"  it will increment 
>>  based on where
>> >you paste it.  So, it will increment up to "=C5" if you 
>>  paste it in
>> >the next cell, or "=D4" if you paste below.
>> >
>> >How can I suppress that, so that when I paste the new 
>>  cell receives
>> >"=C4" as well.
>> >
>> >(I know I can simply copy the text and paste that in, but 
>>  I want to
>> >copy a whole row of formulas to be the same.  I thought
>> >Paste>Special>Formulas would work, but it seems to be the 
>>  same as
>> >regular paste)
>> >
>> >Thnx in advance
>> >
>> >Ross.
>> >.
>> >

0
Gord
11/24/2003 9:47:35 PM
Reply:

Similar Artilces:

Macro for single to multiple cols. Help!
Single col excell spreadsheet example (rows) all data in col A 1) john doe 2) nice guy 3) 1010 space street 4) Nasa Florida 5) (data separated by 1 row here) 6) Jane Smith 7) Nice girl 8) 1011 Space st 9) Nasa Florida 10) etc........... Wishing to populate each row 1-4 into A-D cols. repeating multiple times into just the 4 cols A-D without spaces between the rows. example..... (col A) (B) (C) (D) 1)john doe/nice guy/1010 space street/Nasa Florida 2)Jane Smith/Nice girl/1011 Space st/Nasa Florida 3) etc..... AND!!! same macro but populati...

Copy A Dialog from Project to Project
Sometimes I do this but I get all sorts of errors. I know I need to move the .cpp & .h that go with the dialog, to the new project but I'm not sure what the procedure is for doing this. I seem to get all sorts of errors such as .... enum( ID = IDD_Dialog). Is there a magic way of importing a dialog (and it's Class) from one project to another? Thanks There are serious issues about the resource.h file. You can't just copy the .cpp and .h files, because without PIECES of the resource.h file, they aren't going to be valid. Why would you have a dialog w...

How can I get a copy of Publisher 2002?
I still have Windows 98 on my computer. A client of mine wants me to use Publisher for a project, but I don't want to upgrade to XP right now. How can I get a new copy of PUblisher 2002 that would be compatible with my computer? I did a google search and am uncomfortable about buying from web sites that I don't know. Any suggestions? Hi Crystal1365 (Crystal1365@discussions.microsoft.com), in the newsgroups you posted: || I still have Windows 98 on my computer. A client of mine wants me || to use Publisher for a project, but I don't want to upgrade to XP || right now. ...

Copy error
When I select a large area and copy, a warning msg popup "Picture is too large and will be truncated", sam situation when paste them to a new area after I close the warning window How could I avoid this? The following MSKB article has information that may help: XL2000: Error Message: The Picture Is Too Large and Will Be Truncated http://support.microsoft.com/default.aspx?id=318265 Jason Pan wrote: > When I select a large area and copy, a warning msg popup "Picture is too large and will be truncated", same > situation when paste them to a new area after I close t...

Can't Copy and Paste foumulas?
I am trying to copy an unprotected worksheet and paste it into another worksheet. All of the text gets pasted but the formulas do not. When I try Paste Special the dialog box offers to paste a 'Microsoft Excel Worksheet Object'. And if I try to copy and paste only one cell which contains a formula the same happens. Why is this happening? And is there a way I can paste the cell contents which are formulas? Thank you, Emory Instead of coping all the cells of one worksheet into another, just copy the worksheet itself: Edit > Move or Copy Sheet... -- Gary''s Student &...

Sum of the last (x) values in a row
How would I get the average of the sums of the just previous 52 cells in a row that contains more than 52 columns? TIA... Try this *array* formula: =AVERAGE(INDEX(1:1,LARGE(COLUMN(1:1)*(1:1<>""),52)):IV1) -- Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. If you have less then 52 numbers, the formula will average whatever you have entered. -- HTH, ...

copy only a portion of a spreadsheet
This may duplicate... How do you copy only a portion of a spreadsheet to another location ? ...

Copy and Paste problem
Hi, I have made macros to copy sheet form one file and paste into another, however somtimes there is a problem when for example I change print area in target file or anything else I recive a message: Error the target area is not the same size as the copy area what is starnge as area doesnt change, only solution i found is simply delete the file and make a new one. Im sure you have same problem but did you find a solution ? Piotr, I'm not sure what the Print Area has to do with copying and pasting. Have you determined for sure which statement in your code is generating the error? ...

How do I automatically hide rows
I wish to be able to automatically hide a row when data is entered into a cell in that row. Any ideas? Roy right click sheet tab>view code>copy/paste this>SAVE Now when you enter anything in any cell in rows (below row 4), the row hides Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row > 4 Then Rows(Target.Row).Hidden = True End Sub -- Don Guillett SalesAid Software donaldb@281.com "RobRoy" <RobRoy@discussions.microsoft.com> wrote in message news:1809F151-80EC-4513-8AC2-673E7D5EF91D@microsoft.com... > I wish to be able to automatically hide...

Copy Value Function
Has anyone ever hear of an Excel function called Copy Value apparently it was in some earlier release but has been remove in the new releases of the product. A elderly friend asked me to find out anything about it I could. From what he told me the fuction allowed some values be copied from one set of cells to another and then they could be sorted within the cells it was copied to. Sounds a little funny to me. Any help would be appreciated. Thanks /jim Could it be Copy > Paste Special > Values? If so that built in function is alive and well in all later Excel versions. It simply repl...

Absolute Rows Question
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a data set where the sales in the 1st qtr I am tracking in in row 3 and go from Column B to Column P. I am trying to create a formula where on a separate part of the workbook I can lists those sales. I am entering the following formula. &quot;=B$3&quot; the next row down should read &quot;=C$3&quot;. When I copy the formula, it stays at B$3 all the way down. What am I doing wrong. <br><br>Thanks in advance! Hi Clay, When copying down, only the row reference can change, and...

Excel row highlighter
Is there a way to make the highlight on a row number stretch across the entire row? Thanks Take a look here: http://cpearson.com/excel/rowliner.htm In article <6BECB198-FC45-4E92-A109-A5DC7A6EF416@microsoft.com>, "I_am_a_MS_user" <IamaMSuser@discussions.microsoft.com> wrote: > Is there a way to make the highlight on a row number stretch across the > entire row? > > Thanks Chip Pearson as a RowLiner addin that may interest you: http://www.cpearson.com/excel/RowLiner.htm -- Jim "I_am_a_MS_user" <IamaMSuser@discussions.microsoft.co...

Moving Row Data to Column/Row Groups
First off...great forum with super smart participants. Hopefully, I ca tap into some of this brain power!! :) I have received an Excel emai spreadsheet containing hundreds of names and corresponding emai addresses. Problem is the data runs on one line from A1 to GB1. Fo example, A1 B1 C1 D1 E1 F1, etc (name 1)(email 1)(name 2)(email 2)(name 3)(email 3) etc. I need to break the data to a two column format with the first colum containing the name and the second column containing the emai address...then dropping down to the next row and listing the next set then the next and so on. The good ne...

How copy several sheets from one Excel file to another excel file?
Hi! I would like to copy several sheets from one Excel file to another excel file. Is there any easy way to do that instead of copying one at a time. Is it also possible the sheet name is automatically copied? Please advise, Aijun. -- Ai_Jun_Zhang ------------------------------------------------------------------------ Ai_Jun_Zhang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25474 View this thread: http://www.excelforum.com/showthread.php?threadid=389219 Select first sheet then hold CRTL key while selecting other sheets one at a time or hold SHIFT key...

Need to create rows from columns
I have a spreadsheet that looks like this: Name | item 1 | item 2 | item 3 (etc.) I need to format it into: Name | item 1 Name | item 2 Name | item 3 Does anyone know how I can do that? Thank you - Pat Transfer everything to a second sheet. A B 1 Name 1 2 Name 2 3 Name 3 Use this for A1 =Sheet1!$A$1 and this for B1 =INDEX(Sheet1!$B$1:$D$1,ROW()) Both formulas can be copied down When done, Copy | Paste Special - Values and you will have a reformatted sheet of data. Expand the $B$1:$D$1 as needed for your data. -- If this helps, plea...

cannot print graphics copied from word 2007 for PCs into an existing word document on my mac
Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello everyone, <br><br>I'm new to the forum and hope somebody can help. I stumbled across mactopia whilst searching for an answer to my question in google and read the thread titled &quot;drawings/graphics don't print&quot;. Unfortunately that has not solved my problem, which I outline further below. <br><br>I work from home on my mac (Mac OS X 10.5 Leopard). Currently I am writing up a document in word 2008 for macs. I have some graphics (png. files) that I created on my work computer, ...

getting the number of the row with the maximum value
i have a a column with values, by using a macro i want to get the number of the row in which the maximum value of the column is. Are you sure you want to do it by macro? First you'd use the MAX() function to find the largest value. Now, if you want to do this by macro (presumably to SELECT the cell containing the largest value), you'd use the Find command. I'm assuming your values are in a range named 'rngValues' Dim r As Range Set r = Range("rngValues") r.Find(WorksheetFunction.Max(r)).Select If you don't need a macro, then you'd use the...

list box row source
Access 2003 On my form called f018ContrPerfEmissGua (PK GuaID SK ProjectID) I have a list box called UnitType. I need help with the row source If UnitNo=0, listbox shows all UnitType where ProjectID = mef018ContrPerfEmissGua.ProjectID else listbox shows UnitType where UnitID = mef018ContrPerfEmissGua.UnitD How can I make this work for rowsource in a listbox or combobox? Thanks, -- deb Could you make the rowsource a query with an IIF statement to select that for you? -- Milton Purdy ACCESS State of Arkansas "deb" wrote: > Access 2003...

Lots of rows and columns into just rows
I have a sheet of data 3000+ rows with 8 columns in each. I need to adjust the data into 24000 rows of one column. I want to take the first row of the old sheet (8 columns) and make the contents the first 8 rows of the new sheet (1 column), the old sheet second row to be new rows etc...9-16 Something to do with Paste Special but not sure. Any help please? Cheers, Bodster Say data is on Sheet1, from A1 to H3000. Enter this formula *anywhere* you wish on another sheet, and copy down 3000 rows: =INDEX(Sheet1!$A$1:$H$3000,ROWS($1:8)/8,MOD(ROWS($1:1)-1,8)+1) -- HTH, RD ------------...

Copy named range, but skip blanks
My goal here is to copy "NewRecord" and paste values only in the first blank cell in column A on the Payment History sheet, while skipping blanks rows. "NewRecord" has 22 rows with formulas in columns A, B & C. Sometimes all 22 rows will have data entered, sometimes only the 1st row. The marco works, but does not skip the blank rows in NewRecord - is it because there are formulas present? How do I fix this? Also, how do I assure my macro is pasting on the first blank row of column A? Application.Goto Reference:="NewRecord" Se...

copy Outgoing mails via the server
how can i copy all the outgoing mails via the server to another mail box ? click properties on the store object, and there you have an option to fowored all mails to a specific mailbox. "Indika Gamage" <indikag@hotmail.com> wrote in message news:04d501c46e2b$a7c06730$a401280a@phx.gbl... > how can i copy all the outgoing mails via the server to > another mail box ? what u mean by store object? pls explane the steps >-----Original Message----- >click properties on the store object, and there you have an option to >fowored all mails to a specific mailbox. &...

Excel 2002: How to identify row of data ?
Hi, Happy New Year to you. I have the following table: A B C D E Total Type 1 0 -100 0 +100 0 0 B 2 0 0 0 0 0 0 A 3 0 0 0 0 0 0 A 4 -500 0 +500 0 -750 +750 B I would like to assign row type as "A" when all the cell...

Excel beginner: counting rows in a external file
Hi all. Very basic question: I need to fill a cell of my spreadsheet with th number of lines(row) in an external files (that is automaticall created by another tool). Which is the best/easy way to do it? thanks in advanc -- Message posted from http://www.ExcelForum.com use this is yourworkbook.xls is open Sub oss()' IF open MsgBox Workbooks("yourworkbookt.xls").Sheets("sheet1").Range("c24:c27").Rows.Count End Sub -- Don Guillett SalesAid Software donaldb@281.com "pimar >" <<pimar.10lhho@excelforum-nospam.com> wrote in message news:p...

Q for Stephen LeBans or Allen Browne re: Row NumberVer2
Stephen or Allen, I have been trying to recreate the row numbering query you have in your example: http://www.lebans.com/rownumber.htm but the numbers in the query do not increment. The return value for the query when it is run, is zero for each record.... what am I missing? On Oct 27, 3:30 pm, Opal <tmwel...@rogers.com> wrote: > Stephen or Allen, > > I have been trying to recreate the row numbering query you have in > your example: > > http://www.lebans.com/rownumber.htm > > but the numbers in the query do not increment. The return value for > the query...

Inserted Picture
I have inserted a picture into a spreadsheet. When I try to move picture, there are multiple copies of the picture there. Is there a way that I can view the underlying code (picture) so that I can see how many copies of the picture are present and delete the extras in one operation? You can show the drawing toolbar and click on the arrow icon. Then you can lasso the area that surrounds those pictures and hit the delete key. This will delete all the pictures, so you may want to save the top picture if you need it. jkbob@optonline.net wrote: > > I have inserted a picture into a spr...