| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need help with a formula for rolling twelve months | Excel Discussion (Misc queries) | |||
| Rolling Formula based on If | Excel Worksheet Functions | |||
| rolling twelvemnoths formula for attendance calendar | Excel Worksheet Functions | |||
| Formula for calculating 12 month rolling period | Excel Discussion (Misc queries) | |||
| Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions | |||
Linear Mode
