how do you specify the current column in a sum function

  • Thread starter Thread starter mr unreliable
  • Start date Start date
M

mr unreliable

hi Group,

I have rows named BEG_INT and END_INT. I am summing a range of rows between
these rows for columns named FY_2001, FY_2002, etc.

The row of sums has (intersection-type) formulas which like this:

=SUM(FY_2001 BEG_INT : END_INT)
=SUM(FY_2002 BEG_INT : END_INT)
=SUM(FY_2003 BEG_INT : END_INT)

While this does work ok, I would prefer to have the same formula extended
across the summation row.

How would I go about defining a named function "CurrentColumn", so that I
could use the same formula across my row of sums?

tiafah, jw
 
mr unreliable said:
The row of sums has (intersection-type) formulas which like this:

=SUM(FY_2001 BEG_INT : END_INT)
=SUM(FY_2002 BEG_INT : END_INT)
=SUM(FY_2003 BEG_INT : END_INT)

While this does work ok, I would prefer to have the same formula extended
across the summation row.
Click to expand...
....

If you mean you want to use the same formula in all cells so that you could
enter it in the leftmost column then fill it right rather than having to
edit the column component, then try

=SUM(INDIRECT("C:C",0) Beg_Int:End_Int)

INDIRECT("C:C",0) is the current column given in R1C1 addressing syntax. You
could even make this more readable by defining the name __CurrentColumn__
referring to =INDIRECT("C:C",0), then writing the SUM formulas as

=SUM(__CurrentColumn__ Beg_Int:End_Int)
 
=SUM(INDIRECT("C:C",0) Beg_Int:End_Int)

INDIRECT("C:C",0) is the current column given in R1C1 addressing syntax.
Click to expand...

How does Excel know this is what you meant, rather than column C?
 
How does Excel know this is what you meant, rather than column C?
Click to expand...

This may be a novel concept: read online help for the INDIRECT function,
specifically its second argument. Then it may become clear.
 
Harlan Grove said:
This may be a novel concept: read online help for the INDIRECT function,
specifically its second argument. Then it may become clear.
Click to expand...

OK I indulged in the novel concept and I see what is happening but wouldn't:

=SUM(INDIRECT("C",0) Beg_Int:End_Int)

work just as well?

Regards

Sandy
 
Back
Top