Rolling 12 moth formula
 

I would like to create a formula in Col A that would go out 12 columns (B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns (B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

The formula still changes when I do that.

"JLatham" wrote:

Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns (B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

Which version of Excel works that way, as a matter of interest?
In Excel 2003, inserting a new column B will convert =SUM($B1:$M1) to
=SUM($C1:$N1)

The answer which I would give to Doug's question is =SUM(INDIRECT("B1:M1"))
--
David Biddulph

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns
(B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

Maybe

=SUM(INDIRECT("B1:M1"))

Mike

"Doug" wrote:

The formula still changes when I do that.

"JLatham" wrote:

Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns (B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

=SUM(INDIRECT("B1:M1")

Hope that helps.
--
John C


"Doug" wrote:

The formula still changes when I do that.

"JLatham" wrote:

Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns (B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

That did the trick. Thank you very much.

"Mike H" wrote:

Maybe

=SUM(INDIRECT("B1:M1"))

Mike

"Doug" wrote:

The formula still changes when I do that.

"JLatham" wrote:

Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns (B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

Glad I could help and thanks for the feedback

Mike

"Doug" wrote:

That did the trick. Thank you very much.

"Mike H" wrote:

Maybe

=SUM(INDIRECT("B1:M1"))

Mike

"Doug" wrote:

The formula still changes when I do that.

"JLatham" wrote:

Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns (B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

Probably what I get for writing without testing. Your (and Mike H's)
Indirect would seem to work much, much better. Glad I got caught in my error
early on.

But seems it OUGHT to work that way. If I I referred to Range("$B1:$M1") in
code it sure as hell wouldn't change I don't care how many rows or columns
you inserted, I'd get the value in cells B1 through M1 regardless.

"David Biddulph" wrote:

Which version of Excel works that way, as a matter of interest?
In Excel 2003, inserting a new column B will convert =SUM($B1:$M1) to
=SUM($C1:$N1)

The answer which I would give to Doug's question is =SUM(INDIRECT("B1:M1"))
--
David Biddulph

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns
(B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

If I I referred to Range("$B1:$M1") in
code it sure as hell wouldn't change I don't care how many rows or columns
you inserted, I'd get the value in cells B1 through M1 regardless.

And I think we can all agree you would but likewise if you inserted Rows the
1 wouldn't change to 2 either despite it being relative. I can see the point
of the dollar sign in Vb in things like 'activecell.formula ="=sum($A...' etc
but what is the purpose or point of $ in VB in instances like the one you
note?

Mike

"JLatham" wrote:

Probably what I get for writing without testing. Your (and Mike H's)
Indirect would seem to work much, much better. Glad I got caught in my error
early on.

But seems it OUGHT to work that way. If I I referred to Range("$B1:$M1") in
code it sure as hell wouldn't change I don't care how many rows or columns
you inserted, I'd get the value in cells B1 through M1 regardless.

"David Biddulph" wrote:

Which version of Excel works that way, as a matter of interest?
In Excel 2003, inserting a new column B will convert =SUM($B1:$M1) to
=SUM($C1:$N1)

The answer which I would give to Doug's question is =SUM(INDIRECT("B1:M1"))
--
David Biddulph

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns
(B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

Well, my big question is do $'s actually anchor the moth's to excel? How did
the OP actually get the 12 moth's into excel, cuz I think that would be a far
more difficult question.

:)
--
John C


"Mike H" wrote:

If I I referred to Range("$B1:$M1") in
code it sure as hell wouldn't change I don't care how many rows or columns
you inserted, I'd get the value in cells B1 through M1 regardless.

And I think we can all agree you would but likewise if you inserted Rows the
1 wouldn't change to 2 either despite it being relative. I can see the point
of the dollar sign in Vb in things like 'activecell.formula ="=sum($A...' etc
but what is the purpose or point of $ in VB in instances like the one you
note?

Mike

"JLatham" wrote:

Probably what I get for writing without testing. Your (and Mike H's)
Indirect would seem to work much, much better. Glad I got caught in my error
early on.

But seems it OUGHT to work that way. If I I referred to Range("$B1:$M1") in
code it sure as hell wouldn't change I don't care how many rows or columns
you inserted, I'd get the value in cells B1 through M1 regardless.

"David Biddulph" wrote:

Which version of Excel works that way, as a matter of interest?
In Excel 2003, inserting a new column B will convert =SUM($B1:$M1) to
=SUM($C1:$N1)

The answer which I would give to Doug's question is =SUM(INDIRECT("B1:M1"))
--
David Biddulph

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns
(B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?

Rolling 12 moth formula
 

Mike, as in the example I gave - the $ serves no useful purpose in VBA.
However, if setting up a formula to be written to a cell, it would.
Bottom line here is that at the time I wrote my initial response, apparently
my body was contorted into a rather uncomfortable position and my head was in
a rather dark, dank location! However, thanks to John C, I have a plausible
excuse: had we been working with moths and not months or cells on an Excel
worksheet, my answer may have actually worked ;).

"Mike H" wrote:

If I I referred to Range("$B1:$M1") in
code it sure as hell wouldn't change I don't care how many rows or columns
you inserted, I'd get the value in cells B1 through M1 regardless.

And I think we can all agree you would but likewise if you inserted Rows the
1 wouldn't change to 2 either despite it being relative. I can see the point
of the dollar sign in Vb in things like 'activecell.formula ="=sum($A...' etc
but what is the purpose or point of $ in VB in instances like the one you
note?

Mike

"JLatham" wrote:

Probably what I get for writing without testing. Your (and Mike H's)
Indirect would seem to work much, much better. Glad I got caught in my error
early on.

But seems it OUGHT to work that way. If I I referred to Range("$B1:$M1") in
code it sure as hell wouldn't change I don't care how many rows or columns
you inserted, I'd get the value in cells B1 through M1 regardless.

"David Biddulph" wrote:

Which version of Excel works that way, as a matter of interest?
In Excel 2003, inserting a new column B will convert =SUM($B1:$M1) to
=SUM($C1:$N1)

The answer which I would give to Doug's question is =SUM(INDIRECT("B1:M1"))
--
David Biddulph

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Yes, simply change the formula in column A to
=SUM($B1:$M1)
that'll keep the column references from changing even when you insert the
new column B.

"Doug" wrote:

I would like to create a formula in Col A that would go out 12 columns
(B -
M) and then be able to insert a new column B and have the formula remain
=Sum(B1:M1) and not change to =Sum(C1:N1). Is that possible?