Number cells from 1 to 10 based on values being place in other cells

Posted on 2012-08-22
Last Modified: 2013-08-06
To simplify this question for the sake of getting the help necessary, if I have 2 columns of 10 cells each in Excel and I wanted to place numbers from 1 to 10 in the right column based on when the columns to the left have text placed in them, how would I do that?

So let's say my cell range is A1:B10. If someone put any word, say "Mike", in cell A4, then cell B4 would populate with a 1. If they next put "Bill" in A10, then cell B10 would populate with a 2. The numbers in the right column would continue to increment until I have 10 cells numbered 1 through 10.

Any suggestions? Thanks in advance!
Question by:ambientsbs
    LVL 18

    Expert Comment

    You would need a reference key for this.
    The below file gives an example.
    You can hide the columns I and J so that the reference table is not visible.


    This example does it via number, however you can easily change it to the other way by changing the formula to:

    =IF(A2="","Enter Name",(INDEX($I$6:$I$15,MATCH(A2,$J$6:$J$15,0))))

    You can also add the names as a drop down list.

    Author Comment

    Thanks for the help, but this doesn't accomplish what I'm looking to do. I don't want the number to be associated with a certain name, but rather the order in which I typed the name in. So if the first name I typed in was "John" a number 1 would appear in the column next to John. If I typed in "Steve" a few cells below it, a number 2 would appear in the column next to Steve. The names will be completely random and there's no telling out of my 10 cells which one I will enter a name into first, second, etc.
    LVL 18

    Expert Comment

    OK that makes it harder and would probably require VBScript.

    What is the purpose of doing this however? Knowing that might make it easier to come up with a solution or an alternative.

    Also is the range you are entering names in finite and contiguous, or are you adding names in random cells in the entire workbook?
    LVL 8

    Accepted Solution

    Can you try this file if it is what you needed? Type values in column A.
    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article will show you how to use shortcut menus in the Access run-time environment.
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now