Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    Does anybody know how to move a formula from 1 cell to another making sure that the formula does not change, eg

    Cell 1 has ='Sheet 1'!$A5

    I need Cell 2 to contain exactly the same but I do not want to put a $ in front of the 5.

    The reason is that I have a spreadsheet which has loads of sheets linked and I need to move a row in one sheet but retain the correct pointer to the other sheet.

    Thanks

    Mike

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by mikeyt View Post
    Hi

    Does anybody know how to move a formula from 1 cell to another making sure that the formula does not change, eg

    Cell 1 has ='Sheet 1'!$A5
    .........
    If you want to move it, have you tried a Cut and Paste?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Tim

    Yep tried that and all it does is move the cell identifier, using $ will fix the cell point but thewill not allow it to move if I add rows in the other sheet.

    Regards

    Mike

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Mike - The example shows a $ in front of the A. Is that how yours is set up?

    Can you post a short workbook of what you are trying to do?

  5. #5
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    One way to do a whole bunch of cells is to do a find and replace of = with #. This turns all the formulas into text. You can then copy and paste without the cell references changing. After the move is complete, do a find and replace of # with = and you are back in business.

  6. #6
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    the other way, of course, is to make the reference static using the $ sign. This must be in front of both the row reference and the column reference. Thus your formula would become:

    ='Sheet 1'!$A$5

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Mercyh,

    Thanks for this, the find/replace option is the best, using the $A$5 is not an option because the 5 could move, but I know the A will never move so $A is fine but $5 is not.

    So I will use the find/replace option.

    Thnaks for that.

    Mike

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could just copy and paste it from the formula bar.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    You could just copy and paste it from the formula bar.
    That works fine if it is just one or two cells. If it is a whole bunch of cells it is a bit tedious.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Well, yeah, but if it's a lot of cells, I'd use code.
    Mind you, I don't really understand the scenario here based on the description - it sounds as though you would just move the cells.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Rory,

    You know much more about excel than I will ever be able to learn (based on some of your posts here). I have written a fairly complicated workbook that has large ranges of cells on separate sheets that need to have the same formula (which includes 6 nested if statements) accept for one cell reference difference. I have tried all kinds of ways to move this range of cells and always run into the issue of cell references changing. I have found that, for me, the simplest has been to convert all the formulas to text, copy them to the new locations, do a find and replace within that range of the cells for the reference that needs to be changed and then converting back to a formula. I am sure that you would have a more efficient way to do this. Would you be willing to elaborate on your method using code to move formulas from one range of cells to another without changing the references?

    (these moves are not done on a daily basis by users, only in the authoring process which may get changed or updated once or twice a year).

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    To copy the formulas exactly as they are, you could use something like this:
    Code:
    Sub CopyFormulas()
        Dim rngFrom As Range, rngTo As Range
        Dim varFormulas
        On Error Resume Next
        Set rngFrom = Application.InputBox(prompt:="Select range to copy formulas from", Title:="Copy from:", Type:=8)
        If rngFrom Is Nothing Then Exit Sub
        Set rngTo = Application.InputBox(prompt:="Select range to copy formulas to", Title:="Copy to:", Type:=8)
        If rngTo Is Nothing Then Exit Sub
        varFormulas = rngFrom.Formula
        rngTo(1).Resize(UBound(varFormulas, 1), UBound(varFormulas, 2)).Formula = varFormulas
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Nice,

    Thanks very much.

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by rory View Post
    Code:
        rngTo(1).Resize(UBound(varFormulas, 1), UBound(varFormulas, 2)).Formula = varFormulas
    Thank you for that Rory.

    Is there any advantage to either of the following snippets over the other?
    Code:
        varFormulas = rngFrom.Formula
        rngTo(1).Resize(UBound(varFormulas, 1), UBound(varFormulas, 2)).Formula = varFormulas
    Code:
        rngTo(1).Resize(rngFrom.Rows.Count, rngFrom.Columns.Count).Formula = rngFrom.Formula
    Regards
    Don

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Not particularly. I prefer the former as it's easy to watch what is going into the variable and it cuts down on the number of times you read properties of the range, but in performance terms I doubt there's any real difference.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •