Active Column Ref In a Formula

G

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(A1:D1) 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
 
G

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
 
G

Glynn Taylor

Hello Gary's Student

Thank you for your help.

I'll give it a try

Regards
Glynn
 
D

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

Roger Govier

Hi

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