formula not being put into new row
 

i have a spreadsheet with conditional formatting and formulas in 2 o
the columns. the formula is based on 2 adjacent columns which ar
sometimes blank (but WILL be filled in at a later date). The proble
is when inserting a new row, it isnt copying the formula in these
columns into the new row, yet it IS copying the conditiona
formatting.

Why would it not copy the formula when the same formula is in ever
single row above it (even if the formula results in a blank cell value
the formula itself is still in every cell). also strange it copies th
conditional formatting, you would think if its going to copy it then i
would copy the formatting AND the formula, or neither.

anyway the person inputting the data doesnt know very much so i nee
the formulas to be automatically put into the new row when its inserte
(like it should be anyway).

under tools/options/edit i have ticked 'extend list formats an
formulas' so it should be copying it right? i insert a new row in th
middle of 10 other rows which are all filled in and all contain th
formula, and it leaves the cell completely blank

--
Message posted from http://www.ExcelForum.com

formula not being put into new row
 

Hi
not a build-in feature of Excel. But you may try the
following macro for this:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

-----Original Message-----
i have a spreadsheet with conditional formatting and
formulas in 2 of
the columns. the formula is based on 2 adjacent columns
which are
sometimes blank (but WILL be filled in at a later date).
The problem
is when inserting a new row, it isnt copying the formula
in these 2
columns into the new row, yet it IS copying the
conditional
formatting.

Why would it not copy the formula when the same formula
is in every
single row above it (even if the formula results in a
blank cell value,
the formula itself is still in every cell). also strange
it copies the
conditional formatting, you would think if its going to
copy it then it
would copy the formatting AND the formula, or neither.

anyway the person inputting the data doesnt know very
much so i need
the formulas to be automatically put into the new row
when its inserted
(like it should be anyway).

under tools/options/edit i have ticked 'extend list
formats and
formulas' so it should be copying it right? i insert a
new row in the
middle of 10 other rows which are all filled in and all
contain the
formula, and it leaves the cell completely blank.


---
Message posted from http://www.ExcelForum.com/

.

formula not being put into new row
 

The "copying the format" feature on a row insert has been in Excel for many
versions and is not controlled by the Options setting you mentioned. The
copying the formula feature is newer (Excel 2000 I believe) and it's not
always clear how and when it will work. When it does work though it's on a
delayed basis. Inserting a row never copies the formula immediately, but if
you make an entry in the range the formulas reference, then the formula is
copied (sometimes).

It does seem though that having the referenced range empty kills the copy.
I could get it to work only if there was data there.

This article may help understand how it's supposed to work:

http://support.microsoft.com/default...02&Product=xlw

--
Jim Rech
Excel MVP
"neowok " wrote in message
...
|i have a spreadsheet with conditional formatting and formulas in 2 of
| the columns. the formula is based on 2 adjacent columns which are
| sometimes blank (but WILL be filled in at a later date). The problem
| is when inserting a new row, it isnt copying the formula in these 2
| columns into the new row, yet it IS copying the conditional
| formatting.
|
| Why would it not copy the formula when the same formula is in every
| single row above it (even if the formula results in a blank cell value,
| the formula itself is still in every cell). also strange it copies the
| conditional formatting, you would think if its going to copy it then it
| would copy the formatting AND the formula, or neither.
|
| anyway the person inputting the data doesnt know very much so i need
| the formulas to be automatically put into the new row when its inserted
| (like it should be anyway).
|
| under tools/options/edit i have ticked 'extend list formats and
| formulas' so it should be copying it right? i insert a new row in the
| middle of 10 other rows which are all filled in and all contain the
| formula, and it leaves the cell completely blank.
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|