Active Column Ref In a Formula

[Glynn Taylor]

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

 

[Gary''s Student]

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

 

[Glynn Taylor]

Hello Gary's Student

Thank you for your help.

I'll give it a try

Regards
Glynn

 

[Dave Peterson]

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.

 

[Roger Govier]

Hi

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