My problem is summarised as follows:

A1 to, say, G1 contains a list of numbers

Formulas read:
In A1 =A1
In B1 =Sum(A1:B1)
In C1 =Sum(A1:C1)
In D1 =Sum(A11) etc... to =Sum(A1:G1)

Is it possible to have a formula which can datermine the column reference of
the cell containing the formula?
Some common element that replaces B1, C1, D1 etc. in the above example?

Thank you

If you insert:

=SUM(INDIRECT("A1:" & SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()-1),3)),"$","") &
"1"))

you will get the sum of the values from A1 thru just before the column
containing the formula. For example, if you put the formula in Z1 you will
get the sum from A1 thru Y1
--
Gary''s Student - gsnu200769


"Glynn Taylor" wrote:

> My problem is summarised as follows:
>
> A1 to, say, G1 contains a list of numbers
>
> Formulas read:
> In A1 =A1
> In B1 =Sum(A1:B1)
> In C1 =Sum(A1:C1)
> In D1 =Sum(A11) etc... to =Sum(A1:G1)
>
> Is it possible to have a formula which can datermine the column reference of
> the cell containing the formula?
> Some common element that replaces B1, C1, D1 etc. in the above example?
>
> Thank you
>
>
>

Hello Gary's Student

Thank you for your help.

I'll give it a try

Regards
Glynn

"Gary''s Student" wrote:

> If you insert:
>
> =SUM(INDIRECT("A1:" & SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()-1),3)),"$","") &
> "1"))
>
> you will get the sum of the values from A1 thru just before the column
> containing the formula. For example, if you put the formula in Z1 you will
> get the sum from A1 thru Y1
> --
> Gary''s Student - gsnu200769
>
>
> "Glynn Taylor" wrote:
>
> > My problem is summarised as follows:
> >
> > A1 to, say, G1 contains a list of numbers
> >
> > Formulas read:
> > In A1 =A1
> > In B1 =Sum(A1:B1)
> > In C1 =Sum(A1:C1)
> > In D1 =Sum(A11) etc... to =Sum(A1:G1)
> >
> > Is it possible to have a formula which can datermine the column reference of
> > the cell containing the formula?
> > Some common element that replaces B1, C1, D1 etc. in the above example?
> >
> > Thank you
> >
> >
> >

You may want to restate your question if Gary's Student's response doesn't work
for you.

Your formulas contain the cell that holds the formula. This will result in a
circular reference error.



Glynn Taylor wrote:
>
> My problem is summarised as follows:
>
> A1 to, say, G1 contains a list of numbers
>
> Formulas read:
> In A1 =A1
> In B1 =Sum(A1:B1)
> In C1 =Sum(A1:C1)
> In D1 =Sum(A11) etc... to =Sum(A1:G1)
>
> Is it possible to have a formula which can datermine the column reference of
> the cell containing the formula?
> Some common element that replaces B1, C1, D1 etc. in the above example?
>
> Thank you

--

Dave Peterson

Hi

in B2 enter
=SUM($A$1:INDEX(1:1,COLUMN(B1)))
and copy across as far as required

--
Regards
Roger Govier

"Glynn Taylor" <(E-Mail Removed)> wrote in message
news:618BE108-28EA-4E27-B57D-(E-Mail Removed)...
> My problem is summarised as follows:
>
> A1 to, say, G1 contains a list of numbers
>
> Formulas read:
> In A1 =A1
> In B1 =Sum(A1:B1)
> In C1 =Sum(A1:C1)
> In D1 =Sum(A11) etc... to =Sum(A1:G1)
>
> Is it possible to have a formula which can datermine the column reference
> of
> the cell containing the formula?
> Some common element that replaces B1, C1, D1 etc. in the above example?
>
> Thank you
>
>
>