Extend formats and formulas

J

JRB

Does anyone know why a running sum type of formula =SUM(B2,C1) does not
automatically copy down?
Similarly, I cannot get a fill colour to extend down when a date format is
used.
Formats for numbers and text values copy down OK. It is just dates and the
running sum type of formula that do not seem to work. I have followed all
suggestions for extended formats and formulas in the Knowledge Base.

PS Excel 2000 Vs 9.0.3821 SR-1

Many thanks
Robert
 
A

Anne Troy

Change the formula to =B2+C1. There is no need for the SUM part if you're
only adding two numbers. You're just unnecessarily adding "weight" to your
file.
************
Anne Troy
www.OfficeArticles.com
 
J

JRB

Thanks Anne
I tried your suggestion. The formula still does not 'extend' down to the new
row
 
D

David McRitchie

Hi Robert,

You have to have 3 rows immediately above with the same formatting,
bank statement with a debit or a credit filled in, but not both would
not be the same format. You would have to be lower down on the
sheet for the fill formulas and formatting to work.

I would suggest that for a formula on row 2 that
=B2 + OFFSET(C2,-1,0)
would work better if you want to rearrange your rows, or insert or
delete rows to prevent you from getting a #REF! error.
Normally you would have column headers (labels) at the top of each
column to indicate what is in the column.

see http://www.mvps.org/dmcritchie/excel/insrtrow.htm
 
J

JRB

David thnks for your reply. I have many rows above the new row that have
identical data types and formatting. I tried the formula you sugest and it
produces the running sum that I require, but this formula also does not copy
down when I fill in the rest of the new row at the bottom of the list.
I have found that a formula that references only cells in the same row as
the formula copy down successfully. If the formula references a cell in the
row above as is needed by a running sum, then the extending down of formulas
does not seem to work.

David McRitchie said:
Hi Robert,

You have to have 3 rows immediately above with the same formatting,
bank statement with a debit or a credit filled in, but not both would
not be the same format. You would have to be lower down on the
sheet for the fill formulas and formatting to work.

I would suggest that for a formula on row 2 that
=B2 + OFFSET(C2,-1,0)
would work better if you want to rearrange your rows, or insert or
delete rows to prevent you from getting a #REF! error.
Normally you would have column headers (labels) at the top of each
column to indicate what is in the column.

see http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

JRB said:
Does anyone know why a running sum type of formula =SUM(B2,C1) does not
automatically copy down?
Similarly, I cannot get a fill colour to extend down when a date format is
used.
Formats for numbers and text values copy down OK. It is just dates and the
running sum type of formula that do not seem to work. I have followed all
suggestions for extended formats and formulas in the Knowledge Base.

PS Excel 2000 Vs 9.0.3821 SR-1

Many thanks
Robert
Click to expand...
Click to expand...
 
J

JRB

Thanks Anne, but I am referring to the 'Extend Formats and Formulas' that
applies to a list having consistant data, formatting and formulas in the rows
above. Simply want a basic running sum formula to copy down automatically
when I fill in a new row at the bottom of the list.
 
D

David McRitchie

Yes, I find that extend formulas and formats is unreliable and of little
use, but I do keep it turned on. If you look at the webpage, I referred
you to you will find other solutions. such as invoking a macro to
insert rows. An event macro to fill in the formatting and formulas
once you enter something into a column.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

JRB said:
David thnks for your reply. I have many rows above the new row that have
identical data types and formatting. I tried the formula you sugest and it
produces the running sum that I require, but this formula also does not copy
down when I fill in the rest of the new row at the bottom of the list.
I have found that a formula that references only cells in the same row as
the formula copy down successfully. If the formula references a cell in the
row above as is needed by a running sum, then the extending down of formulas
does not seem to work.

David McRitchie said:
Hi Robert,

You have to have 3 rows immediately above with the same formatting,
bank statement with a debit or a credit filled in, but not both would
not be the same format. You would have to be lower down on the
sheet for the fill formulas and formatting to work.

I would suggest that for a formula on row 2 that
=B2 + OFFSET(C2,-1,0)
would work better if you want to rearrange your rows, or insert or
delete rows to prevent you from getting a #REF! error.
Normally you would have column headers (labels) at the top of each
column to indicate what is in the column.

see http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

JRB said:
Does anyone know why a running sum type of formula =SUM(B2,C1) does not
automatically copy down?
Similarly, I cannot get a fill colour to extend down when a date format is
used.
Formats for numbers and text values copy down OK. It is just dates and the
running sum type of formula that do not seem to work. I have followed all
suggestions for extended formats and formulas in the Knowledge Base.

PS Excel 2000 Vs 9.0.3821 SR-1

Many thanks
Robert
Click to expand...
Click to expand...
Click to expand...
 
J

JRB

Thanks David, will give the macro route a try. Pity extend formulas does not
work as advertised, it seems a pretty useful idea.
Robert

David McRitchie said:
Yes, I find that extend formulas and formats is unreliable and of little
use, but I do keep it turned on. If you look at the webpage, I referred
you to you will find other solutions. such as invoking a macro to
insert rows. An event macro to fill in the formatting and formulas
once you enter something into a column.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

JRB said:
David thnks for your reply. I have many rows above the new row that have
identical data types and formatting. I tried the formula you sugest and it
produces the running sum that I require, but this formula also does not copy
down when I fill in the rest of the new row at the bottom of the list.
I have found that a formula that references only cells in the same row as
the formula copy down successfully. If the formula references a cell in the
row above as is needed by a running sum, then the extending down of formulas
does not seem to work.

David McRitchie said:
Hi Robert,

You have to have 3 rows immediately above with the same formatting,
bank statement with a debit or a credit filled in, but not both would
not be the same format. You would have to be lower down on the
sheet for the fill formulas and formatting to work.

I would suggest that for a formula on row 2 that
=B2 + OFFSET(C2,-1,0)
would work better if you want to rearrange your rows, or insert or
delete rows to prevent you from getting a #REF! error.
Normally you would have column headers (labels) at the top of each
column to indicate what is in the column.

see http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Does anyone know why a running sum type of formula =SUM(B2,C1) does not
automatically copy down?
Similarly, I cannot get a fill colour to extend down when a date format is
used.
Formats for numbers and text values copy down OK. It is just dates and the
running sum type of formula that do not seem to work. I have followed all
suggestions for extended formats and formulas in the Knowledge Base.

PS Excel 2000 Vs 9.0.3821 SR-1

Many thanks
Robert
Click to expand...
Click to expand...
Click to expand...
 
D

David McRitchie

Hi Robert,
Extend formulas and formats works as advertised it's just that are so many
little things that can break the chain, like empty spots (cells) on a row.
 
J

JRB

Hi David,
I have used the simplest of lists and formulas to test this. With my version
of Excel, a formula that references cells in the Same row ALWAYS copies down.
A formula that references a cell in any other row NEVER copies down. Formats
(eg fill colour) in a date column never copies down.
Perhaps there is a patch that fixes this for Excel 2000?
Rgds
Robert
 
M

marisky23

Hi,

I have just trying to figure out the extend formats and formula
feature for a family member. The feature will not extend a formula tha
has been copy/pasted into the row. To avoid having to type a formula i
each time you need to use Fill/Down instead of copy paste. I also hav
found that if the formula uses more than 2 columns you need
consectutive rows in order to get the sum to automatically appear.
also was able to successfully get a simple formua like =G2-G1 t
automatically extend by using the previous tip of =G2-Offset(G2,-1,0).

In addition if the formula has even the simplest of differences such a
number formatting or even =sum(A1:G1) vs. =+sum(A1:G1) the extend won'
work (until there are 5 consecutive rows with the same formula).

Hope this helps you.

Jennife
 
Top