EXCEL: Insert Cell Without Changing Formula
Solved
julien
-
gaelle -
gaelle -
Hello,
I would like to know if it is possible to ensure in EXCEL (v2000) that inserting cells does not impact formulas.
For example:
G12 = A12 + F12
Currently, if I insert a cell in F12, then G12 becomes:
G12 = A12 + F13
I would like G12 to remain:
G12 = A12 + F12
I haven't found the solution, please let me know if it exists.
Thank you in advance for your help,
Julien
I would like to know if it is possible to ensure in EXCEL (v2000) that inserting cells does not impact formulas.
For example:
G12 = A12 + F12
Currently, if I insert a cell in F12, then G12 becomes:
G12 = A12 + F13
I would like G12 to remain:
G12 = A12 + F12
I haven't found the solution, please let me know if it exists.
Thank you in advance for your help,
Julien
Configuration: Windows XP Firefox 3.0 Excel 2000
16 réponses
Hello Vaucluse and others,
For my solution, there is no link with the insertion above; I could just as easily have used A1 or Z65000 as an intermediate cell, and I could also not use any if I enter the cell reference in indirect().
Which gives in G12:
=A12 + indirect("F12")
Whatever insertions/deletions on the sheet, indirect("F12") will always point to F12
No need for a macro, nor to suspend the calculations that will be updated as soon as we reactivate them (and we will inevitably have to do so sooner or later...)
Eric
For my solution, there is no link with the insertion above; I could just as easily have used A1 or Z65000 as an intermediate cell, and I could also not use any if I enter the cell reference in indirect().
Which gives in G12:
=A12 + indirect("F12")
Whatever insertions/deletions on the sheet, indirect("F12") will always point to F12
No need for a macro, nor to suspend the calculations that will be updated as soon as we reactivate them (and we will inevitably have to do so sooner or later...)
Eric
Alright Eric
Except at the end... you cannot get a result of A2 + F12 by using the formula A2 + INDIRECT(F12) since INDIRECT expects a reference that you cannot put in F12. (result: #REF)
You necessarily have to go through an intermediate cell.
Right?
Aside from this point which is just meant to tease you a bit, the solution is excellent, although it requires a special organization of the data, regardless of the position of the transfer cell
Once again, well done, but that will be all for this time :-)
Best regards
Kind regards
P.S.: That said, it would have been nice of Excel to create an option on the subject.
--
Truth is not possessed, it is only sought.
Except at the end... you cannot get a result of A2 + F12 by using the formula A2 + INDIRECT(F12) since INDIRECT expects a reference that you cannot put in F12. (result: #REF)
You necessarily have to go through an intermediate cell.
Right?
Aside from this point which is just meant to tease you a bit, the solution is excellent, although it requires a special organization of the data, regardless of the position of the transfer cell
Once again, well done, but that will be all for this time :-)
Best regards
Kind regards
P.S.: That said, it would have been nice of Excel to create an option on the subject.
--
Truth is not possessed, it is only sought.
Hello,
It's possible by using the INDIRECT function.
Enter in a cell (for example in F1) the text reference of the desired cell, that is, F12.
in G12:
=A12+INDIRECT(F1)
Eric
It's possible by using the INDIRECT function.
Enter in a cell (for example in F1) the text reference of the desired cell, that is, F12.
in G12:
=A12+INDIRECT(F1)
Eric
For those who are still interested, a simple solution is to first replace all the '=' in the table and replace them, for example, with '..' (Ctrl F).
This way, we can easily copy and paste the table without the formulas changing because they are not recognized by Excel as formulas.
Then we do the reverse operation, replacing '..' with '='.
Bye ;-)
This way, we can easily copy and paste the table without the formulas changing because they are not recognized by Excel as formulas.
Then we do the reverse operation, replacing '..' with '='.
Bye ;-)
Hello everyone,
3 little questions:
1. Does this insertion operation take place in a specific area of the sheet?
2. Does this insertion always push downwards?
3. Is it on a single cell?
The idea being a macro...
Michel
3 little questions:
1. Does this insertion operation take place in a specific area of the sheet?
2. Does this insertion always push downwards?
3. Is it on a single cell?
The idea being a macro...
Michel
I completely agree with Vaucluse, I had already tried this option, it still causes delays; the only action is that it doesn't calculate in real-time but only when we request it: so it's even worse.
I'm disgusted, there must be a solution...
I'm searching but can't find anything...
Anyone else?
I'm disgusted, there must be a solution...
I'm searching but can't find anything...
Anyone else?
Re
At least we agree. I just tried a little trick (it's the day for tricks) that might work for you:
I entered your formula in G12
I cut an empty cell from F11
I went to F12: Right click / Insert cut cell / Shift down and there, to my surprise =A12+F12 remained unchanged.
In conclusion, the formula shifts when you insert a cell, but does not move when you insert a cut empty cell (note: cut, not copied)
Best regards
--
The truth is not owned, it is only sought.
At least we agree. I just tried a little trick (it's the day for tricks) that might work for you:
I entered your formula in G12
I cut an empty cell from F11
I went to F12: Right click / Insert cut cell / Shift down and there, to my surprise =A12+F12 remained unchanged.
In conclusion, the formula shifts when you insert a cell, but does not move when you insert a cut empty cell (note: cut, not copied)
Best regards
--
The truth is not owned, it is only sought.
Hello
I can't tell you why, but it works well for me (note simple formulas (sum and VLOOKUP), I haven't tried with a complex formula)
In D5 I put 1
In E5 I put 2
In F5 I put =sum(D5:E5)
Then I go to Tools\Options\Tab 'Calculation'\Check 'Manual'
I go to D3, I right-click/Insert/Shift cells up or down
F9 to calculate and I have my correct result.
Sorry for not being able to provide more.
I can't tell you why, but it works well for me (note simple formulas (sum and VLOOKUP), I haven't tried with a complex formula)
In D5 I put 1
In E5 I put 2
In F5 I put =sum(D5:E5)
Then I go to Tools\Options\Tab 'Calculation'\Check 'Manual'
I go to D3, I right-click/Insert/Shift cells up or down
F9 to calculate and I have my correct result.
Sorry for not being able to provide more.
Re Mabelle:
Without error or misunderstanding on my part:
We cannot fault you, but with your demonstration, if you find 3 in F5 after inserting D3 in D5, it does prove that the formula has become D6+E5 since your D5 has now become equal to 0.
Going through the option is unnecessary in this case
Isn't it?
Kind regards
--
The truth is not possessed, it is only sought.
Without error or misunderstanding on my part:
We cannot fault you, but with your demonstration, if you find 3 in F5 after inserting D3 in D5, it does prove that the formula has become D6+E5 since your D5 has now become equal to 0.
Going through the option is unnecessary in this case
Isn't it?
Kind regards
--
The truth is not possessed, it is only sought.
Hello everyone,
3 little questions:
1/ does this insertion operation take place in a specific area of the sheet?
2/ does this insertion always push downwards?
3/ on a single cell?
the idea being a macro...
Michel
3 little questions:
1/ does this insertion operation take place in a specific area of the sheet?
2/ does this insertion always push downwards?
3/ on a single cell?
the idea being a macro...
Michel
no no Vaucluse
neither error nor misunderstanding. Just a lack of explanation on my part
My total is indeed 3 at first and then 2 when I insert downwards.
neither error nor misunderstanding. Just a lack of explanation on my part
My total is indeed 3 at first and then 2 when I insert downwards.
Hello
=A12 + INDIRECT("f" & ROW() + 1)
and here we can insert without changing the formula!!!
francis
=A12 + INDIRECT("f" & ROW() + 1)
and here we can insert without changing the formula!!!
francis
Hello Francis,
I am very interested in your latest formula; however, I do not understand all the elements of this formula and I would like some additional information!
Thank you :)
I am very interested in your latest formula; however, I do not understand all the elements of this formula and I would like some additional information!
Thank you :)
Hello,
You know, he answered a bit of nonsense to a question asked 2 years ago which was:
how to insert a row without changing the references of a formula?
He turned the answer into something more complicated by writing it in a more complex way =A12+F13 (because his formula boils down to that), the inserted line actually cancels out the indirect()....
Better start a new discussion by explaining your problem
eric
You know, he answered a bit of nonsense to a question asked 2 years ago which was:
how to insert a row without changing the references of a formula?
He turned the answer into something more complicated by writing it in a more complex way =A12+F13 (because his formula boils down to that), the inserted line actually cancels out the indirect()....
Better start a new discussion by explaining your problem
eric
Examine the creation of named ranges, especially if those ranges are arrays.
EX: A named range Test = F1:F15, G12 = A12+Test. Inserting a cell in F12 will not change the formula in G12.
EX: A named range Test = F1:F15, G12 = A12+Test. Inserting a cell in F12 will not change the formula in G12.
Hello, I found a solution that works, and it's really simple:
what I wanted to do was also copy a complete row of formulas without changing the references.
so by copying row 1 where my formulas were, for example cell A1 = Feuil2!A1, cell B1 = Feuil2!B1 etc...
I wanted that when pasting in row 2, my formula remains the same:
Cell A1 = Feuil2!A1, cell B1 = Feuil2!B1 etc...
I've tried everything before without it working!! I've lost some hair, oh well ;o)
And I found a solution:
First, I create a new sheet that I call "Formule",
I copy all the formulas from my row 1 (sheet1) by removing the "="
so, I just have plain text.
Then, when I want to duplicate my row while keeping the cell references,
I copy the row from my "Formule" sheet,
I go to sheet 1: cell A2, and I paste the entire line of text.
my formulas are therefore correct, but since there's no "=" sign, it doesn't work!
So I highlight the entire row,
I press "Ctrl + H" to open "replace"
and there, I indicate:
replace: Feuil2!
with: =Feuil2!
replace all, and that's it.
I use this technique for a client table, which redirects me to each sheet I create for each client.
So I already create the client's sheet by copying my model table, then I replace Feuil2! with =ClientName!
and it works ;o)
there may be a simpler way, but I don't have much time to look ;o)
what I wanted to do was also copy a complete row of formulas without changing the references.
so by copying row 1 where my formulas were, for example cell A1 = Feuil2!A1, cell B1 = Feuil2!B1 etc...
I wanted that when pasting in row 2, my formula remains the same:
Cell A1 = Feuil2!A1, cell B1 = Feuil2!B1 etc...
I've tried everything before without it working!! I've lost some hair, oh well ;o)
And I found a solution:
First, I create a new sheet that I call "Formule",
I copy all the formulas from my row 1 (sheet1) by removing the "="
so, I just have plain text.
Then, when I want to duplicate my row while keeping the cell references,
I copy the row from my "Formule" sheet,
I go to sheet 1: cell A2, and I paste the entire line of text.
my formulas are therefore correct, but since there's no "=" sign, it doesn't work!
So I highlight the entire row,
I press "Ctrl + H" to open "replace"
and there, I indicate:
replace: Feuil2!
with: =Feuil2!
replace all, and that's it.
I use this technique for a client table, which redirects me to each sheet I create for each client.
So I already create the client's sheet by copying my model table, then I replace Feuil2! with =ClientName!
and it works ;o)
there may be a simpler way, but I don't have much time to look ;o)
Hello
By enabling the 'on order' calculation option, we have the ability to shift cells without affecting the formula.
Tool\Option\Tab 'Calculation'\Check 'On Order'
See you+
By enabling the 'on order' calculation option, we have the ability to shift cells without affecting the formula.
Tool\Option\Tab 'Calculation'\Check 'On Order'
See you+
I don't believe there is a solution!
Blocked or not, indeed the codes change.... unless we insert cells or rows below those concerned by the formula
For example here on F13
Best regards
--
The truth is not possessed, it is only sought