Extend formats and formulas

[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

[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

"JRB" <[email protected]> wrote in message
news:[email protected]...
> 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

[JRB]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 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
---
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" <[email protected]> wrote in message news:[email protected]...
> 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

[Anne Troy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[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" 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" <[email protected]> wrote in message news:[email protected]...
> > 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
>
>
>

[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.

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
> > > 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
> >
> >
> >

[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" 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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> > > > 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 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.

"JRB" <[email protected]> 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

[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

"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" <[email protected]> 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
>
>
>

[marisky23]

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