Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel AutoComplete (Office 97)

    When entering data into a spreadsheet not all words will autocomplete. The enable autocomplete is ticked within Tools Options. Any suggestions on how to fix this problem. Any help appreciated.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    193
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    Hi englishrose,

    One problem with Auto Complete is that it relies on contiguous entries in columns, so if you skip a row you lose access to the entries you had above. A way around this is obviously not to have blank cells in the autocomplete columns by entering a space or an apostrophe in any empty row cell (Any other suitable character will suffice - it's just that space and apostrophe are less visible).

    Good Luck!

    Peter Moran

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    Thanks Peter

    The entries we have in Excel are actually contiguous. No blank cells or apostrophe's shown. Any other suggestions?

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    You may want to take a look at this article. Autocomplete apparently does not work if it has been more than 102 rows since the last occurrence of the entry.

    Microsoft Knowledge Base Article - Q177423
    XL97: AutoComplete Fails After 102 Rows Past Last Occurrence

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    Thanks for that. However, the spreadsheet is only 69 rows. Any other suggestions?

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    How similar are the entries? Can you attach an example and let's see if ours does the same.

    -Lenny

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    Sorry for some reason I can't attach the file

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    Can you email it to me directly?

    lennyishere@yahoo.com

    -Lenny

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    SOLVED.

    Okay, I got your spreadsheet. (You couldn't attach it to this forum because it was too large.)

    Anyway, it's a real simple issue. You merely have a space after some of your entries. I'm sure they were entered accidently. One entry might be "Nissan" and another "Nissan_". This causes Excel to see two different entries that start with n-i-s-s-a-n. Therefore, Excel can't autocomplete until the last character is typed... which looks like it's not autocompleting at all. Did that make sense?

    Simply find the cells that have the extra space after the word and delete the space. You can run down each cell and hit F2 (means edit) and the cell will appear with a blinking cursor. If the cursor is touching the last character in the cell, it's okay. If there is space between the last character and the cursor, there's a space. Remove it.

    After you do that, you'll find the AutoComplete works just fine. There's also a function (Trim) that will remove such extra spaces. But since you have only a few instances, I think manual removal would be more efficient.

    Let me know if that works or if you need more help.

    -Lenny

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    EnglishRose, I hope you checked the forum for my solution. I evidentally posted it in reply to my own earlier post, so I got the notification.

    While I'm here, though, let me give you another way to clear those pesky spaces...

    1) Select the cells in the Make column that contain data. (Do not select the whole column.)
    2) Click Replace in the Edit Menu.
    3) In "Find What" type a space.
    4) Click "Replace All".
    5) Take a coffee break, you're done.

    That might make the task a little slicker. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    -Lenny

  11. #11
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoComplete (Office 97)

    Sorry for not responding early, haven't accessed my email etc all weekend as I haven't been working. Thanks so so much for that i removed the spaces and autocomplete now works. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •