Insert new row copying formulas from above?

[DD999]

I thought there would be an easy way to insert a row and keep the formulas from the rows above, but if there is, I haven't found it. No faqs or previous posts seem to have covered it.

What is the best way to do this?

It's funny that conditional formatting does seem to work for newly inserted rows but formulas don't.

 

[Chr1sUK]

The way I always do it is select the entire row you want to copy, copy it, then select the entire row(s) you want to paste to, and then right click, select paste special and choose formulas.

Hope this helps.

 

[shatch]

If you highlight the cells on a row (not the entire row) and then click on the box in the bottom right of the selection and drag it down one row, then let go, you will copy all of the formulas.

 

[SkipVought]

DD,

You have to be careful with Insert & Delete IF you have references to previous rows.

Is this just a one time thing, or something you will be doing alot?

I prefer to add my data at the bottom of my table, using just using Copy 'n' Paste and then SORT the table into some order. Alot simpler and less likely to screw thing up this way.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Chr1sUK]

Whenever I have inserted rows, the references etc. have updated themselves with no problesms

 

[st24v6]

My 2 penneth!
Copy entire row
Right click
Insert copied cells.
This will move the row and any totals down as you go


PJ

"If you don't know how, ask 1st"

 

[DD999]

Hi Folks,

Thanks for the replies. I had tried the things mentioned but was hoping that I had missed something like an option setup that said "insert new rows with formulas" or something like that.

I will be doing this a lot, but all of the formulas use relative references so there's not much risk that way. This is the forula: =IF(AND(J$75>=$F77,J$75<=$G77),$H77,"")

I wonder if it's worth writing a macro that will insert a row above the selected cell and copying the formula in the required cells?

Thanks again!

 

[KenWright]

Have you tried Data / Form?

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Oops - That assumed that your data was being added at the end, which it may not.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[sawedoff]

If you will customize the toolbar, by adding a button for "Insert Row" you can then select a row, click copy, then go to the row below where you want the new one, and click the new button, and it will insert a row with formulas. For some reason, this only works when you have the button on the toolbar, but not when you click Insert-Row from the menu.



Sawedoff