=value(cell) converts text into numbers as an FYI. It won’t really work in conjunction with the sum function, though.
In the spreadsheet I set up earlier (in Excel 2002), sum(value(A1), value(A2)) gives the appropriate answer. Of course, this varies by Excel version.
The single nicest feature they could add to Excel is the ability to apply a single cell function to an array on the fly.
If there are any experts that could help me with this one, I would be grateful.
When you make many text entries in a single column, often Excel will guess at the rest of the entry after a character or two. What I find is that in some sheets, it only works for some columns. In column D for example, it guesses at the entry and saves me some time. But in column F it refuses to guess and I do a lot of typing. Any way to format a column to have it try to complete a text entry with similar previous entries?
BobDiLem
That is called the “auto-complete” feature. I don’t think your problem is the way you have formatted the columns as opposed to what you have in each column. Example:
Send
Receive
Transfer
When you type a 4th word in that column if the first letter is an ‘s’, ‘r’ or ‘t’ Excel will “fill in” the entire word according to that first letter.
Now let’s suppose you type in a 4th word in that column and the word is “total”. When you type the ‘t’ it “auto-completes” to “Transfer” which you don’t want. So keep on typing to get the word Total in there.
Now let’s suppose you want to type a 5th word in that column - the word ‘transfer’. When you type the ‘t’, notice the auto-complete no longer fills in on the first letter. when you type the 2nd letter ‘r’, then the auto-complete fills in the rest of ‘transfer’.
So, it all depends upon the words that are already in that column.
BobLibDem
Sorry for the misspelling of your name - I guess I made a tpyo !! :smack:
Anyway, I wanted to add, that if you want to see the ON or OFF status of the auto-complete feature, go to TOOLS, OPTIONS, then click on the EDIT tab.
I see what you’re saying, but I’m still baffled by why autocomplete can be on in column D and off in column F. You can’t set that by column can you?
BobLibDem
Well, you are right because I just tried setting auto-complete off for one column. It doesn’t work. It seems to be an “all-or-nothing” feature.
You can try the Excel help feature and type in “auto complete” (without the hyphen) A lot of help topics will appear. Here’s one that may be helpful.
The current data region Check the location of the active cell; it may be outside the current data region. For example, if the data region is the range of cells A1:E10, Microsoft Excel completes an entry typed in cell A11, even if the cell A10 above it is blank. However, Microsoft Excel does not complete an entry in cell A12, because that cell is outside the data region.
I hope that helps. I am unfamiliar with setting data regions. Maybe that’s where the problem lies.
“Data Region” is not an actual attribute you can set. It just refers to, basically, a chunk of data. If you have many spaces somewhere down a column, Excel may ‘forget’ that there are autocomplete candidates above it.