Dyamic Ranges
 

I was wondering if i could do this, if so how?

I have a sum that goes through the same row. Everytime i insert a column i
want it to automatically to include that inserted range.

Example:
A B C D
1
2
3

Cell C1 is suming Ranges A1:B1. Now if i insert the column before column C,
i want the sum to be able to pick up that inserted range to sum ranges A1:C1
automatically

is there a way??

Dyamic Ranges
 

=SUM(INDIRECT("A"&ROW()&":"&ADDRESS(ROW(),COLUMN()-1)))

--
Regards,
Tom Ogilvy


"Andy" wrote in message
...
I was wondering if i could do this, if so how?

I have a sum that goes through the same row. Everytime i insert a column
i
want it to automatically to include that inserted range.

Example:
A B C D
1
2
3

Cell C1 is suming Ranges A1:B1. Now if i insert the column before column
C,
i want the sum to be able to pick up that inserted range to sum ranges
A1:C1
automatically

is there a way??

Dyamic Ranges
 

Thanks Tom,

Is there a way to do in VB????

"Tom Ogilvy" wrote:

=SUM(INDIRECT("A"&ROW()&":"&ADDRESS(ROW(),COLUMN()-1)))

--
Regards,
Tom Ogilvy


"Andy" wrote in message
...
I was wondering if i could do this, if so how?

I have a sum that goes through the same row. Everytime i insert a column
i
want it to automatically to include that inserted range.

Example:
A B C D
1
2
3

Cell C1 is suming Ranges A1:B1. Now if i insert the column before column
C,
i want the sum to be able to pick up that inserted range to sum ranges
A1:C1
automatically

is there a way??

Dyamic Ranges
 

What is in column C. Constants that represent the sum of A and B or a
formula that sums A and B.

--
Regards,
Tom Ogilvy

"Andy" wrote in message
...
Thanks Tom,

Is there a way to do in VB????

"Tom Ogilvy" wrote:

=SUM(INDIRECT("A"&ROW()&":"&ADDRESS(ROW(),COLUMN()-1)))

--
Regards,
Tom Ogilvy


"Andy" wrote in message
...
I was wondering if i could do this, if so how?

I have a sum that goes through the same row. Everytime i insert a
column
i
want it to automatically to include that inserted range.

Example:
A B C D
1
2
3

Cell C1 is suming Ranges A1:B1. Now if i insert the column before
column
C,
i want the sum to be able to pick up that inserted range to sum ranges
A1:C1
automatically

is there a way??

Dyamic Ranges
 

Hi Andy,

If you're interested,

An alternative approach that's really easy to use directly in cells, or with
VBA, is using local Defined Names with relative references. For example, in
your scenario I would assign cell "C1" the name "'SheetName'!LeftCell" (minus
the quote characters) in the Name box. Then I would highlight the contents of
the "RefersTo:" box and select cell "B1". The resulting address that appears
in the RefersTo box will have "$" characters, which must be removed to make
the reference relative to C1 as one cell to the left. Now, any cell where you
use this name in a formula will reference the cell to its immediate left.

The formula you enter directly in a cell is "=SUM(A1:LeftCell)". I find this
technique much more descriptive than using the INDIRECT() function, and more
flexible.

Obviously, you can do the same for any offset reference you want. Here's
some suggestions:

LastLeft LastCell LastRight
LeftCell (Target) RightCell
NextLeft NextCell NextRight

Relative to "(Target)", you can easily see which cell is being referenced,
"(Target)" being the cell that contains the formula using the locally Defined
Name.

You can enter the formula with VBA as follows:
ActiveCell.Formula = "=SUM(A1:LeftCell)" (Be sure to include the quote
characters)

Enjoy...
GS