Excel Questions

[Dave]

Last night I was building a spreadsheet to track a project I'm working
on. I'm writing a book, in fact, and I wanted to track progress by date
and by chapter, with a few extra columns for things like total words
written.

It turned out to be fairly large, by my standards, some 30 columns by
200 rows. My first problem came with the totals column. I wanted to put
the sum of columns E to Y in the Z column. Excel flagged an error every
time, wanting to add columns C and D to the sum. Since C contained the
date and D some other information, I was sure I didn't want them, but
could I find a way to persuade Excel? Could I heck as like. Any ideas on
how I could do that?

Second problem came after I'd laid out the sheet. I wanted to hide
columns G to Y and a bunch of the rows, so I was only looking at the
part of the sheet I was working on. Hiding the rows went as expected. I
selected column G, shift clicked on column Y and blow me, Excel selected
the whole sheet! So, I thought, I'll have to do this the slow way.
Selected column G, clicked on "Hide" and the *?*?*?* program hid every
column up to W! What's more I could find no way of unhiding them.

Ideas for getting round this annoying "feature" would be gratefully
received. I'm running Excel from Office 2004 with the latest patch under
OS 10.4.8 on a dual core Mac Mini with 1GB of RAM.

And, yes, my machine finally stopped glowing blue!!

Cheers,

Dave

 

[Bob Greenblatt]

Dave,

First, there is a news group specifically for Excel:
microsoft.public.mac.office.excel. Anyway, find my comments below.

Last night I was building a spreadsheet to track a project I'm working
on. I'm writing a book, in fact, and I wanted to track progress by date
and by chapter, with a few extra columns for things like total words
written.

It turned out to be fairly large, by my standards, some 30 columns by
200 rows.

This may be large by your standards, but by no means is it a large
spreadsheet. I (and many others) work regularly with multiple sheets of 200
columns or so by thousands of rows. So, The size of your sheet has nothing
to do with the problems you are seeing.

My first problem came with the totals column. I wanted to put
the sum of columns E to Y in the Z column. Excel flagged an error every
time, wanting to add columns C and D to the sum. Since C contained the
date and D some other information, I was sure I didn't want them, but
could I find a way to persuade Excel?

What was the error? Are you using a list? Is Excel trying to convert your
data to a list? Specifically, what happens when you enter =sum(e1:y1) in
column Z? (Change the 1 to represent the row where you are entering the
formula.)

Could I heck as like. Any ideas on
how I could do that?

What does this mean?

Second problem came after I'd laid out the sheet. I wanted to hide
columns G to Y and a bunch of the rows, so I was only looking at the
part of the sheet I was working on. Hiding the rows went as expected. I
selected column G, shift clicked on column Y and blow me, Excel selected
the whole sheet! So, I thought, I'll have to do this the slow way.
Selected column G, clicked on "Hide" and the *?*?*?* program hid every
column up to W! What's more I could find no way of unhiding them.

Do you have any merged cells? If so, it is best to get rid of them, for the
above reasons. You can always click on the little diamond to the left of the
horizontal ruler at the column heading level to select the whole sheet. Then
in the format menu, select row- unhide, then column-unhide. Do you have any
utilities that are trapping keys?

Ideas for getting round this annoying "feature" would be gratefully
received. I'm running Excel from Office 2004 with the latest patch under
OS 10.4.8 on a dual core Mac Mini with 1GB of RAM.

Respond to the above questions and we'll go from there.

 

[JE McGimpsey]

My first problem came with the totals column. I wanted to put
the sum of columns E to Y in the Z column. Excel flagged an error every
time, wanting to add columns C and D to the sum. Since C contained the
date and D some other information, I was sure I didn't want them, but
could I find a way to persuade Excel? Could I heck as like. Any ideas on
how I could do that?

What formula did you use? What error message did you get? What does
"Excel...wanting to add columns C and D to the sum" mean? Do you have
merged cells by any chance?

This should work:

Z2: =SUM(E2:Y2)

copy down as far as required.

 

[JE McGimpsey]

Second problem came after I'd laid out the sheet. I wanted to hide
columns G to Y and a bunch of the rows, so I was only looking at the
part of the sheet I was working on. Hiding the rows went as expected. I
selected column G, shift clicked on column Y and blow me, Excel selected
the whole sheet! So, I thought, I'll have to do this the slow way.
Selected column G, clicked on "Hide" and the *?*?*?* program hid every
column up to W! What's more I could find no way of unhiding them.

The only way I can reproduce this is if I have a sheet with merged cells
that span the entire sheet. Do you have merged cells?

One way to unhide - select a row header (e.g., 1) to select an entire
row, then choose Format/Column/Unhide.

 

[CyberTaz]

Hi Dave -

I get the impression you may have used the AutoSum button to generate your
totals. It is typical for the feature to assume that all consecutive cells
containing values are to be summed, but if the range is innacurate you need
only edit it to indicate the correct cell reference(s).

OTOH, if your're referring to the appearance of a SmartTag showing up along
with a flag in the corner of the calculation cell, that's tied to the same
"concept" - but they will pop up even if you write the fx from scratch if
there are additional values in contiguous cells. Just click the Smart Tag &
selecty Ignore Error form the contextual menu. Both pieces of markup will go
away. That's just the MS way of trying to let us know that their software
knows more about what we're tying to do than we do

I'm not sure if you can turn the "feature" off in MacXL, but on the dark
side you can go to Tools>Options>Error Checking & remove the check for
"Formula omits cells in region" - you might check Excel>Preferences to see
if there is a comparable setting... Not at a Mac right now so I can't check
it myself

As to your second ??, I have no idea... Haven't seen it, can't reproduce it.
Perhaps Mr. McGimpsey is on the right track there.

 

[Dave]

Thanks very much for all the advice and apologies for being so slow in
getting back to you. Now for some answers:

Yes, I had a merged title running across all the columns of the sheet.
I'd hidden it when I did a split so I forgot all about it when I was
trying to hide the columns. Unmerging it while I did the hide, then
merging it back solved the problem. Thanks.

As for the calculation problem. The formula I used was:
Z6 = SUM(E6:Y6)
The error message I got was "Formula omits adjacent cells"

One of the cells it wanted to include was the date! Shouldn't have done
that, surely (Cell used a custom format for layout - DD MMM).

The only way I could stop this error being thrown up was by turning off
background error checking, so that's what I've done.

Thanks again,

Dave

 

[JE McGimpsey]

Yes, I had a merged title running across all the columns of the sheet.
I'd hidden it when I did a split so I forgot all about it when I was
trying to hide the columns. Unmerging it while I did the hide, then
merging it back solved the problem. Thanks.

DON'T MERGE!!! - it leads to all the problems you've been having.

Instead, enter the title in the left-most cell. Then select the cells
you would have merged and choose Format/Cells/Alignment, and choose
Center Across Selection in the "Horizontal" dropdown.

Merged cells are almost never worth the problems they cause.

As for the calculation problem. The formula I used was:
Z6 = SUM(E6:Y6)
The error message I got was "Formula omits adjacent cells"

Ah - the "smart tag"! I've ignored them for so long, I don't even think
about them as "errors".

Smart tags are supposed to be helpful tips - i.e., to let you know that
your formula doesn't include all the cells in the row - but they're not
"smart" enough to be able to tell what your intent is.

Frankly, the only XL smart tag rule that's ever been useful to me is the
Number stored as text tag. It can otherwise be difficult to detect when
numbers get pasted in as Text, as they do occasionally, especially if
the source is a web page.

One of the cells it wanted to include was the date! Shouldn't have done
that, surely (Cell used a custom format for layout - DD MMM).

The only way I could stop this error being thrown up was by turning off
background error checking, so that's what I've done.

Another way is to choose Preferences/Error Checking, and uncheck the
"Formula omits cells in region" checkbox.

You can also choose "Ignore Error" from the smart tag's dropdown.

 

[Dave]

DON'T MERGE!!! - it leads to all the problems you've been having.

Instead, enter the title in the left-most cell. Then select the cells
you would have merged and choose Format/Cells/Alignment, and choose
Center Across Selection in the "Horizontal" dropdown.

VMT for this and your other tips.

Cheers,

Dave