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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Change your options

Tools - Options - Edit
click on
Extend list formats and formulas
 

pfunk

New Member
Joined
Nov 15, 2005
Messages
5
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.
 

pfunk

New Member
Joined
Nov 15, 2005
Messages
5

ADVERTISEMENT

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.
 

pfunk

New Member
Joined
Nov 15, 2005
Messages
5
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?
 

pfunk

New Member
Joined
Nov 15, 2005
Messages
5
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,736
Messages
5,573,926
Members
412,555
Latest member
mark84
Top