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

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!
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Elton PascuaConnect With a Mentor Commented:
Can you try this file if it is what you needed? Type values in column A.
RartemassAuthor, martial arts coach, IT ConsultantCommented:
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.
ambientsbsAuthor Commented:
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.
RartemassAuthor, martial arts coach, IT ConsultantCommented:
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?
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
All Courses

From novice to tech pro — start learning today.