EXCEL: Insert Cell Without Changing Formula

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

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

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

He's right again, the rascal.
Hi, I don't have anything more to say, I believe that's THE solution.
P.S: You could have said it right away, did you do it on purpose? :-)
--
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. Is it on a single cell?

The idea being a macro...

Michel

The solution is to put the $ sign:

G12 = $A$12 + $F$12 

that should work :)

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?

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.

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

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.

Hello

=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 :)

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.

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)

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+