How to insert lines without shifting everything?
Solved
Jimy6000
Posted messages
254
Status
Membre
-
Jimy6000 Posted messages 254 Status Membre -
Jimy6000 Posted messages 254 Status Membre -
Hello,
I have a table in Excel 2007, and every time I update my data by inserting a row (of 7 cells), all the formulas shift.
I just entered my data into it, but now that there are formulas depending on this data, I have to modify them every time I input more recent figures.
How can I make the cells move down without constantly changing the formulas?
Thank you for your help...
Jimmy
I have a table in Excel 2007, and every time I update my data by inserting a row (of 7 cells), all the formulas shift.
I just entered my data into it, but now that there are formulas depending on this data, I have to modify them every time I input more recent figures.
How can I make the cells move down without constantly changing the formulas?
Thank you for your help...
Jimmy
Configuration: Windows XP Internet Explorer 7.0
10 réponses
Hello everyone
Here is my problem, I need a macro that copies a row in Excel right below it (the row contains data but no calculations)
Here is my problem, I need a macro that copies a row in Excel right below it (the row contains data but no calculations)
If you use the same formula in different cells, you just need to click on the first cell, and a plus sign will appear; then you can drag it down or move it wherever you want. If you think of changing one of your formulas, make it absolute by pressing F4 between the operations.
For example: =(8F4*6F4)
And for your rows, do you go to the Insert Cells menu? Otherwise, I'm not sure; I hope I helped you.
For example: =(8F4*6F4)
And for your rows, do you go to the Insert Cells menu? Otherwise, I'm not sure; I hope I helped you.
Hello
Normally, the formula takes one cell as a starting value and another as an ending value (in the most common cases), and any rows that may be added between these two cells will be included in the final calculation.
It would therefore be useful to know what type of cell it is and especially what type of formulas (it may be necessary to consider modifying them to take into account a larger number of aspects...?)
Normally, the formula takes one cell as a starting value and another as an ending value (in the most common cases), and any rows that may be added between these two cells will be included in the final calculation.
It would therefore be useful to know what type of cell it is and especially what type of formulas (it may be necessary to consider modifying them to take into account a larger number of aspects...?)
Hello
Can you send your file here, as there may be areas to define or a macro to create
http://www.cijoint.fr/index.php
Can you send your file here, as there may be areas to define or a macro to create
http://www.cijoint.fr/index.php
Hello, Jimmy
I assume that in this operation, you have a table where the first few cells are filled in manually and the following ones have formulas.
In which case:
Insert a full row and delete the cells across the width of the fields containing the formulas, so they move up to the level of the insertion.
Similarly, a simple recorded macro would allow you to adjust the formulas based on this principle (adding/removing in the right place) while performing the insertion.
Just in case, on a copy of your file:
Tool/Macro/New Macro/Respond to the box/OK/Do what you want by inserting and deleting in the desired places/Tool/Macro/Stop recording.
Every time you run this macro (button, keyboard depending on your option), the operation will be repeated.
BCRDLMNT
PS:
1° if you have a lot of manipulations in your macro, open it 'step by step' (same process), and remove all the Smallscroll lines that serve no purpose.
2° if you choose this solution and have any macro issues, please come back to present the code and we will help you.
--
Science without conscience is but the ruin of the soul.
I assume that in this operation, you have a table where the first few cells are filled in manually and the following ones have formulas.
In which case:
Insert a full row and delete the cells across the width of the fields containing the formulas, so they move up to the level of the insertion.
Similarly, a simple recorded macro would allow you to adjust the formulas based on this principle (adding/removing in the right place) while performing the insertion.
Just in case, on a copy of your file:
Tool/Macro/New Macro/Respond to the box/OK/Do what you want by inserting and deleting in the desired places/Tool/Macro/Stop recording.
Every time you run this macro (button, keyboard depending on your option), the operation will be repeated.
BCRDLMNT
PS:
1° if you have a lot of manipulations in your macro, open it 'step by step' (same process), and remove all the Smallscroll lines that serve no purpose.
2° if you choose this solution and have any macro issues, please come back to present the code and we will help you.
--
Science without conscience is but the ruin of the soul.
Hello & thank you all :)
Indeed, I just tried to insert a line starting from the second line and it works...
Actually, what I was doing before was inserting my new line from the top, e.g. (E2, F2, etc...) so everything was shifting but it didn't take into account the new line of data LOL!
However, I'm not very strong in "macros", which line of data should I write to execute the function to shift down: from E3 to K3?
Thanks everyone
Jimmy
Indeed, I just tried to insert a line starting from the second line and it works...
Actually, what I was doing before was inserting my new line from the top, e.g. (E2, F2, etc...) so everything was shifting but it didn't take into account the new line of data LOL!
However, I'm not very strong in "macros", which line of data should I write to execute the function to shift down: from E3 to K3?
Thanks everyone
Jimmy
Jimmy
If you follow the process from my message 4, your macro will write itself.
BCRDLNT
--
Science without conscience is but the ruin of the soul.
If you follow the process from my message 4, your macro will write itself.
BCRDLNT
--
Science without conscience is but the ruin of the soul.
Hello Vaucluse (and everyone)
I just implemented the famous macro to add a row and miracle, it works!!
But the problem remains: it correctly adds the row but the formulas change to E4.... K4 :(
I don’t know how to keep them as E3..F3... etc...
Can someone help me?
Thank you all
Bye
Jimmy
I just implemented the famous macro to add a row and miracle, it works!!
But the problem remains: it correctly adds the row but the formulas change to E4.... K4 :(
I don’t know how to keep them as E3..F3... etc...
Can someone help me?
Thank you all
Bye
Jimmy
Your problem is not very clear.
If you want the formulas to remain matched with your cells after inserting, by shifting the whole table down, just take back your macro, since apparently you have succeeded
I suppose now you find yourself with a table shifted by one row and a row without formulas?
Then take back your macro with the following manipulations, if you insert below row 1
Record
Select row 2
Insert a row
Copy row 3
Special paste on row 2 / Right click / check "Formulas" option
Close the recording
Your formulas will be on row 2 and the rest of the table will not be disturbed
However, the macro only works for inserting on row 2
You have probably noticed that these macros can be triggered from the keyboard by placing your choice in the first box that opens
BCRDLMNT
--
Science without conscience is but the ruin of the soul.
If you want the formulas to remain matched with your cells after inserting, by shifting the whole table down, just take back your macro, since apparently you have succeeded
I suppose now you find yourself with a table shifted by one row and a row without formulas?
Then take back your macro with the following manipulations, if you insert below row 1
Record
Select row 2
Insert a row
Copy row 3
Special paste on row 2 / Right click / check "Formulas" option
Close the recording
Your formulas will be on row 2 and the rest of the table will not be disturbed
However, the macro only works for inserting on row 2
You have probably noticed that these macros can be triggered from the keyboard by placing your choice in the first box that opens
BCRDLMNT
--
Science without conscience is but the ruin of the soul.