Moving a formula from one cell to another

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

You could just copy and paste it from the formula bar.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Nice,

Thanks very much.

Reply | Quote

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.

Reply | Quote

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.

I was surprised to find that although your preferred approach appears to be taking an extra step; it consistently runs 2% faster than the shorter version which I identified.
Go figure.

Reply | Quote

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

Is Cell2 in the same position in the spreadsheet?
Depending on the positionality (is that a word- should be) of Cell1 vs Cell2. Another way to write the formula might be to use the Address, Row & Column functions (possibly offset as well).

Reply | Quote

You can also try the following:

Below should never be used on Array Formulas, it will change the Array formulas into regular formulas.

1. First Create the Following two procedures:
Note “‘” is double quote then single quote then double quote.

Sub Labels()
‘
Dim Cell as Range
‘
For Each Cell In Selection
Cell.Formula = “‘” & Cell.Formula
Next
End Sub

Sub Formulas()
With Selection
.Formula = .Formula
End With
End Sub

2. Select the Formulas you want to copy.
Run the “Labels” Procedure [can be used on contiguous or multiple ranges.]
Copy the data to its new location

3. Select the both the original formulas and the copied formulas
Run the “Formulas” Procedure

Reply | Quote

I’ve found the simplest and most reliable method is to add an apostrophe at the beginning of the formula, turning it into a text line. Then drag or copy and paste into the destination cell(s) and remove the apostrophe. That way your formula references remain exactly as they are

hope that helps

Reply | Quote

Use the (free) ASAP utilities
About 300 functions
One of which is “Copy formulas without changing cell references.”
http://www.asap-utilities.com/

Reply | Quote

I use a simple COPYon the original formula, select destination and the Paste Special/formula. This works on a simple spread sheet I use

Reply | Quote

Hey Giles – thanks for the tip on ASAP Utilities! Man that’s a gem!

Reply | Quote

another approach is to copy the series from the original sheet onto a new sheet, placing the formulas where you’d like them. Excel doesn’t modify the formulas betwen sheets so you can then copy from the new sheet back to the new location on the old sheet. e.g., copy sheet1!a3 to sheet2!x3, then reverse, copy sheet2!x3 to sheet1!x3. This example can expand to do ranges.

Reply | Quote