Atomatically changing text entry to a month

[slinger42]

I have a problem with entering a single Character like a Capital O (That's text not number) into a cell. Excell is automatically changing the single character to October. This will happen whenever I use a single letter entry which is the first letter of a month. The sheet has conditional formatting that colours the cell with O or a \ character to Red for O and green for \. I can force the cell to become an O by editing in the formula bar. I have shown the screenshot as attached . It only seems to happen in a column that coincides with the first date of a given month. ALL the cells are formatted as text. There are no formulas in any of the data entry cells.
Any clues would bee much appreciated.

 

[Anonymous1378]

Try turning off autocomplete?

 

[Peter_SSs]

Another option if you do not want to be turning AutoComplete on and off for various worksheets is to enter a new blank row just below your month headings (as shown below) and then hide that row. The sheet will look exactly the same but the O typed further down will not turn into October.

Yet another option if you also do not want to insert/hide that blank row is to remove the text month names from the top row and replace them with dates (1/09/23, 1/10/23 or whatever your local date format is) and then custom format that top row as "mmmm". Again it will look the same but will not AutoComple the month name when you type the first letter

 

[slinger42]

Try turning off autocomplete?

Thanks, but I ned Autocomplete on

 

[slinger42]

Another option if you do not want to be turning AutoComplete on and off for various worksheets is to enter a new blank row just below your month headings (as shown below) and then hide that row. The sheet will look exactly the same but the O typed further down will not turn into October.

View attachment 96589


Yet another option if you also do not want to insert/hide that blank row is to remove the text month names from the top row and replace them with dates (1/09/23, 1/10/23 or whatever your local date format is) and then custom format that top row as "mmmm". Again it will look the same but will not AutoComple the month name when you type the first letter

Thanks. Worked a treat. just need to change a host of cell references now to get it all back working.
I would be interested to know why it happens though

 

[Peter_SSs]

I would be interested to know why it happens though

It is happening for the same reason that AutoComplete normally works. When there is a sequence of cells in a column and you start to type something in the next cell that Excel recognises could come from above it triggers the offer of AutoComplete.

In the example below, the word November is actually housed in the cell marked with a red cross. The three cells below that (marked with yellow arrows) all have something in in them. So when you type an N in the next cell (circled blue) Excel looks up the column and finds November so offers to AutoComplete it for you. It does not happen in the other 3 'November' columns because November is not in the cell at the top of those columns

Thanks. Worked a treat.

You're welcome. Glad to help.

 

[Peter_SSs]

Thanks. Worked a treat.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.