Ask Experts Questions for FREE Help !
Ask
    eedie's Avatar
    eedie Posts: 1, Reputation: 1
    New Member
     
    #1

    Apr 25, 2009, 01:52 PM
    Want to get numbers to repeat in a spreadsheet like words do.
    If I type a word in a cell on the spreadsheet, and further down the column if I type the beginning letter of that word, it will repeat. However, that only works with words. I cannot find a way to get a number to repeat in that same fashing. I have two columns where a certain number is used almost 100% of the time. Is there a way that I can get the number to repeat the same way that words do.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Apr 25, 2009, 02:18 PM

    No the autocomplete works only with text. However you can create a macro that can type in the number with simple keystroke combination.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Apr 25, 2009, 02:22 PM

    No, there is no builtin autocomplete for numbers. But... you can cheat. Unfortunately it will require some editing of the existing sheet on your part.

    Since autocomplete only works for text, you need to:
    1. Highlight the entire column and format it for TEXT (Ctrl-1, Number: Category: Text)
    2. Now go down your existing data and lets convert IT to text:
      1. In an adjacent empty column enter this formula:
        =A1&""
      2. Change the A1 to whatever the first cell reference is. This will convert the value there into a TEXT representation of the same string.
      3. Copy that cell down the whole column...as far as the data set goes
      4. Now highlight the entire new column of value you've created and press CTRL-C to copy it
      5. Click on the top cell in the original values and click on EDIT > PASTE SPECIAL > Values
      6. Now delete the column you created

    Ok, now that you've converted all the existing data in this column to TEXT, and formatted the column for TEXT, autocomplete will SORT OF work for you, but not automatically.

    Numeric-looking text strings STILL won't pop up on their own like "cat" or "dog" would, but in the next empty cell down you can press ALT-DownArrow and a picklist will appear that includes your numeric-looking text strings.

    That's about the best you can do...
    =========

    A semi-lame idea is to just enter ZZ for that number during a bunch of data entry and then use one SEARCH/REPLACE on that column to convert all the ZZ entries to your special number.

    =========
    Ooh... that makes me think, you could create a Worksheet_Change event that watches that column and anytime it sees you enter ZZ, it changes it to your common number. That would be cool, but you'd have to have macros active all the time.

    If the other ideas above don't suffice and you want to try an active macro, let me know. I can help with that if you don't know how to create the macro.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Apr 25, 2009, 02:30 PM

    Scott's idea is the best. Turn on the macro recorder being sure to assign a keyboard-shortcut as you start it, let it record you typing in the number. Then stop the recorder.

    Now click ALT-F8, click on your new macro and select EDIT.

    Inside the macro you'll see two-line... the one where you entered your code into the active cell and the next cell your cursor selected. Delete that second line.
    Code:
        ActiveCell.FormulaR1C1 = "1234"
        Range("C9").Select   <<<delete this line 
    
    
    
    or change that line above to:
        ActiveCell.Offset(0, 1).Select    'this moves one cell to the right
    Then Alt-Q to close the editor. Save your sheet.

    Now anytime you press your key-combination it will enter your special value.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

MS Excel spreadsheet changing numbers [ 4 Answers ]

I have a spreadsheet where I enter credit card numbers, 16 digits. One note, I have the field setup as a custom format, that displays in the custom format as ####-####-####-####. This way it automatically adds the 4 dashes. For some reason, it always changes the last digit to zero. For...

Letters are numbers and num lock off no numbers [ 2 Answers ]

I cannot get my hp pavilion notebook to respond, it started all of a sudden. When my num lock is on my letters on the keyboard turn into numbers... I=5 and so forth. Now when my num lock is off my keyboard works fine but I cannot use the number pad. I tried the fn key with almost every key on the...

Repeat fan action [ 1 Answers ]

My York electric furnace blower fan runs for 3 minutes, stops then continues. Even after shutting the system down. Avd. Rgds Shoeguy

Can You Repeat Liposuction? [ 1 Answers ]

I had liposuction done on my thighs, but I am still not satisfied with the results and there still appears to be a lot of fat there. Can you have more lipo performed on the same area?

Why does history repeat with the following? [ 3 Answers ]

OK people I have witnessed some interesting behaviour on both sides of the sexes and I've lived through some of these situations on one side or the other but ultimately it's just left me wondering how different people answer some of these questions.. some of them are way way more heavily usually...


View more questions Search