Need help getting columns to aut-sum

[Edward F. Henninger]

Hoping to get some help. An Excel idiot.

Trying to get my Excel to auto-calculate sums at the right end of
columns. I have managed to set this up across a few columns (B through
W), but if I add another column later it doesn't work—and I need to be able
to add columns as the year goes by.

I don't seem to have this problem with another file: no matter how
many columns I add it continues to automatically give me the sum of
the columns in the rightmost column. I know this is probably a simple
thing to do but I can't seem to figure it out. I've tried the help
file but I think I may be looking in the wrong place.

Thanks to all for help.

 

[JE McGimpsey]

Trying to get my Excel to auto-calculate sums at the right end of
columns. I have managed to set this up across a few columns (B through
W), but if I add another column later it doesn't work—and I need to be able
to add columns as the year goes by.

I don't seem to have this problem with another file: no matter how
many columns I add it continues to automatically give me the sum of
the columns in the rightmost column. I know this is probably a simple
thing to do but I can't seem to figure it out. I've tried the help
file but I think I may be looking in the wrong place.

Thanks to all for help.

I could guess at a solution, but perhaps it would be better to see what
your working solution is and see why that's "not working"...

FWIW, "it doesn't work" is one of the most unhelpful phrases that
posters use in these groups. I'm sure you know what it means, but
there's a plethora of things I can think of. Try to be a bit more
descriptive when you post back.

 

[Edward F. Henninger]

I could guess at a solution, but perhaps it would be better to see what
your working solution is and see why that's "not working"...

FWIW, "it doesn't work" is one of the most unhelpful phrases that
posters use in these groups. I'm sure you know what it means, but
there's a plethora of things I can think of. Try to be a bit more
descriptive when you post back.

JE:

Thanks for responding.

The worksheet is a column-by-column set. Name of a client at the top
of each column. Columns are then twelve figures month-by-month, JAN at
top and DEC at bottom. I manually place total of each column (client)
at bottom of each column. Rows are figures for each month's grand
total from all clients. I'm trying to set it up so the monthly figures
will auto-sum. As mentioned in original post, I can get auto-sum with
"B2:W2" but if I add an "X" "Y" or "Z" column, it no longer
operates—probably because it's set to work to "W".

In another file, for some reason or other, it will auto sum across
columns no matter how many columns I add and add and add. That's one
of the reasons I'm confused.

Does this help? If not, I'll be happy to e-mail you a pdf.

Ed

 

[Bob Greenblatt]

JE:

Thanks for responding.

The worksheet is a column-by-column set. Name of a client at the top
of each column. Columns are then twelve figures month-by-month, JAN at
top and DEC at bottom. I manually place total of each column (client)
at bottom of each column. Rows are figures for each month's grand
total from all clients. I'm trying to set it up so the monthly figures
will auto-sum. As mentioned in original post, I can get auto-sum with
"B2:W2" but if I add an "X" "Y" or "Z" column, it no longer
operates—probably because it's set to work to "W".

In another file, for some reason or other, it will auto sum across
columns no matter how many columns I add and add and add. That's one
of the reasons I'm confused.

Does this help? If not, I'll be happy to e-mail you a pdf.

Ed

Ed,

A simple way around this is to insert a blank column between the last client
and the totals. Have the sum include this blank column. Then when you insert
a column to the left of the blank, it will work properly. You can make the
blank column really narrow so it does not take up any significant space.

 

[Edward F. Henninger]

Ed,

A simple way around this is to insert a blank column between the last client
and the totals. Have the sum include this blank column. Then when you insert
a column to the left of the blank, it will work properly. You can make the
blank column really narrow so it does not take up any significant space.

Bob:

An interesting suggestion. I'll have to try it out. But I'm thinking
that surely that's not the way the Excel team wanted this to work.
There has to be a simpler and more elegant answer. It happens in
another file—why not this one?

Ed

 

[Bob Greenblatt]

Bob:

An interesting suggestion. I'll have to try it out. But I'm thinking
that surely that's not the way the Excel team wanted this to work.
There has to be a simpler and more elegant answer. It happens in
another file—why not this one?

Ed

I'm not sure what you are seeing on another file. However, I disagree, and
what you are describing is EXACTLY the way that the Excel team wanted this
to work. If you insert column (or rows) into a range, the formula will
expand to include the inserted columns. In your case, you are inserting a
column at the end of a range. In this case, the formula should NOT expand to
include the new column. This is why my suggestion of including a blank
column is a simple and easy way to make this work in all cases, as you will
be inserting columns within the range covered by the formula.

 

[Dayo Mitchell]

I'm not sure what you are seeing on another file. However, I disagree, and
what you are describing is EXACTLY the way that the Excel team wanted this
to work. If you insert column (or rows) into a range, the formula will
expand to include the inserted columns. In your case, you are inserting a
column at the end of a range. In this case, the formula should NOT expand to
include the new column. This is why my suggestion of including a blank
column is a simple and easy way to make this work in all cases, as you will
be inserting columns within the range covered by the formula.

I have to say, I've been following and testing, and I can't manage to
produce a result where inserting a column at the very end of the range
(select sum column, click insert column icon) does NOT expand the formula to
include the new column. (Excel 2001, Mac OS 9.2.2) Although I've used the
blank column trick, it's really looking like I don't need it at all. I've
typed in the range, selected the range with the mouse, used AutoSum
icon--same results every time. So Bob, how are you managing to do it?

Ed, have you tried re-setting up the formulas in the worksheet that is not
behaving? Have you compared the formulas in the two worksheets that behave
differently? Have you compared the preferences set for the two worksheets?
(maybe some of them are per-worksheet settings).

Since Bob is presumably in Excel X, and I'm in 2001, could the difference be
that the behaving worksheet was created in 2001?

DM

 

[Bob Greenblatt]

I have to say, I've been following and testing, and I can't manage to
produce a result where inserting a column at the very end of the range
(select sum column, click insert column icon) does NOT expand the formula to
include the new column. (Excel 2001, Mac OS 9.2.2) Although I've used the
blank column trick, it's really looking like I don't need it at all. I've
typed in the range, selected the range with the mouse, used AutoSum
icon--same results every time. So Bob, how are you managing to do it?

Ed, have you tried re-setting up the formulas in the worksheet that is not
behaving? Have you compared the formulas in the two worksheets that behave
differently? Have you compared the preferences set for the two worksheets?
(maybe some of them are per-worksheet settings).

Since Bob is presumably in Excel X, and I'm in 2001, could the difference be
that the behaving worksheet was created in 2001?

DM

Dayo and all,

It's not an excel version issue. If in cell D1 you place the formula
=sum(a1:c1), and then click on the column heading for D1 and select
Insert-columns, the formula will remain a1:c1. This is expected and by
design, and, in my opinion, correct.

Now, maybe I don't really understand what you are doing, or what you expect
to happen.

 

[Dayo Mitchell]

Dayo and all,

It's not an excel version issue. If in cell D1 you place the formula
=sum(a1:c1), and then click on the column heading for D1 and select
Insert-columns, the formula will remain a1:c1. This is expected and by
design, and, in my opinion, correct.

Now, maybe I don't really understand what you are doing, or what you expect
to happen.

Okay, here's some info. (Excel 2001)

I did exactly that, set up A1 to C1 with some numbers, typed in the formula
into D1 exactly as you had it. Then I selected the entire column D and
inserted a new column, pushing the formula to E1. Then I clicked in E1.
The formula still said =sum(a1:c1), just as you said it would. (I had never
tried actually looking at the formula at this point before)

*However,* when I typed a number in D1 and hit return, E1 updated the
formula and the sum to include D1. (which is what Ed *wants* to happen but
only sees in some worksheets, and what I always see happening).
Incidentally, after I hit return, E1 briefly flashed black, presumably to
denote it was being updated.

Same behavior if I added number to D1 and hit tab, or hit enter. Same
behavior if I tried clicking a bunch of other cells and typing other text in
between inserting the new column and entering the number in D1. (well,
actually I'm up to J1 by now, but I can't imagine it matters). Same behavior
even if I inserted the column, closed the worksheet, reopened it, and *then*
inserted a number in D1. E1 still updated the formula when I entered a
number in the newly inserted column.

So test it with entering the number--what behavior do you see?

Dayo

 

[Bob Greenblatt]

OK,

Now I'm with you. Sorry for my senility. What's happening is that you (and
Ed) have an option selected. It apparently is not selected on all of Ed's
sheets. Look in Preferences-Edit- Extend List formats and formulas. Check
the Help which explains the conditions when this happens.

(I never use this option as I don't like anybody but me playing with my
formulas.) I find this a pretty dangerous option as there is a high
probability that unintended formulas may change without notice. That's why I
don't use it. If your worksheets are simple and straight forward and you
fully understand what's going on, try it. The option does not appear to be
saved with the workbook, and is a global option affecting all open
workbooks.

 

[Dayo Mitchell]

Aha! The secret! So helpful. And balloon help explains exactly what it
does. I thought I had checked for relevant prefs, but I think I got
distracted before I checked the Edit tab.

I've got pretty simple spreadsheets (that are actually designed with this in
mind), so this is safe for me, but I can see how it could be iffy.

Not sure how Ed could be getting differing behavior, since it's a global
setting, but maybe he's on multiple computers. Which would account for not
having scrutinized the respective prefs settings already.

Thanks,
Dayo

 

[Edward F. Henninger]

Aha! The secret! So helpful. And balloon help explains exactly what it
does. I thought I had checked for relevant prefs, but I think I got
distracted before I checked the Edit tab.

I've got pretty simple spreadsheets (that are actually designed with this in
mind), so this is safe for me, but I can see how it could be iffy.

Not sure how Ed could be getting differing behavior, since it's a global
setting, but maybe he's on multiple computers. Which would account for not
having scrutinized the respective prefs settings already.

Thanks,
Dayo

Bob:

That's it! Thanks to you—and all—for your help. It's great having a
forum like this where you can really get some practical help. Perhaps
someday I'll be able to repay the favor by helping someone else.

Ed