Extend formats and formulas
 

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

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

"JRB" wrote in message
...
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

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

"Anne Troy" wrote:

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

"JRB" wrote in message
...
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

Okay, let's define "extend", then. Or perhaps this article (the bit about
the Fill Handle) will help:
http://www.officearticles.com/excel/...soft_excel.htm
************
Anne Troy
www.OfficeArticles.com

"JRB" wrote in message
...
Thanks Anne
I tried your suggestion. The formula still does not 'extend' down to the
new
row

"Anne Troy" wrote:

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

"JRB" wrote in message
...
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

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" wrote in message ...
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

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" wrote:

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" wrote in message ...
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

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.

"Anne Troy" wrote:

Okay, let's define "extend", then. Or perhaps this article (the bit about
the Fill Handle) will help:
http://www.officearticles.com/excel/...soft_excel.htm
************
Anne Troy
www.OfficeArticles.com

"JRB" wrote in message
...
Thanks Anne
I tried your suggestion. The formula still does not 'extend' down to the
new
row

"Anne Troy" wrote:

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

"JRB" wrote in message
...
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

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" wrote in message ...
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" wrote:

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" wrote in message ...
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

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" wrote:

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" wrote in message ...
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" wrote:

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" wrote in message ...
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

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.

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

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

"David McRitchie" wrote:

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.

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

Hi,

I have just trying to figure out the extend formats and formulas
feature for a family member. The feature will not extend a formula that
has been copy/pasted into the row. To avoid having to type a formula in
each time you need to use Fill/Down instead of copy paste. I also have
found that if the formula uses more than 2 columns you need 5
consectutive rows in order to get the sum to automatically appear. I
also was able to successfully get a simple formua like =G2-G1 to
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 as
number formatting or even =sum(A1:G1) vs. =+sum(A1:G1) the extend won't
work (until there are 5 consecutive rows with the same formula).

Hope this helps you.

Jennifer


--
marisky23
------------------------------------------------------------------------
marisky23's Profile: http://www.excelforum.com/member.php...o&userid=26736
View this thread: http://www.excelforum.com/showthread...hreadid=397774