Formulas not being copied when I insert rows

pfunk

New Member
Joined
Nov 15, 2005
Messages
5
I have a spreadsheet with formulas on many cells in each row that contain IF with absolute and relative references.

When I add a new row in between two perfectly functioning rows, the new row does not contain the formulas that the rows to either side have. How do I get the formulas to automatically be part of the new rows that I add?

thanks,

pfunk
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
pfunk said:
I have a spreadsheet with formulas on many cells in each row that contain IF with absolute and relative references.

When I add a new row in between two perfectly functioning rows, the new row does not contain the formulas that the rows to either side have. How do I get the formulas to automatically be part of the new rows that I add?

thanks,

pfunk

If you are on Excel 2003, convert the area of data and processing into a list by means of Data|List|Create List. What you need will the happen automatically.
 
Upvote 0
Thanks for the 2 quick replies - but neither of them work

texasalynn, My "Extend list formats and formulas" box was already checked.

Aladin Akyurek, I don't have a Data|List|Create List option ... The copyright on my Excel is 2002.
 
Upvote 0
I've been looking for tips on how to make this work and I've tried them all but nothing is working so far. Anyone else have any ideas?

To be clear, the formulas that I'm extending contain IF functions and basic math and they all work when I manually cut/paste them but there is no sign of them when i just add a row in between a bunch of existing rows.
 
Upvote 0
But I don't need to add many rows worth of data at once.

I guess I could make the spreadsheet more complicated by entering the data on one sheet and then create a macro that will format a new row on my main sheet and copy the data over to my main sheet but that seems like overkill for this simple task.

I just figured that there was some setting that would allow me insert a row and preserve the formatting from the previous row ....

Can someone explain WHY the formulas are not being preserved? is it not working because the formatting is row dependent (Row D column 4's function is IF(D3="YES",blah, blah) and obviously I haven't filled in D3 yet since I just created the row?
 
Upvote 0
I just tried the macro that is at the end of the thread you pointed me to and it adds the rows but it still does not copy the formulas.

There must be some setting I have to make it so the formulas are not specifically copied - anyone have any ideas what that could be?
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top