Excel - update one column based on the value of another column 1

[Rmcta]

I am going to have a combo box in Excel.
I need the user to pick a name from the combo box and when the user does, I wish Excel to insert a number into another column which I will keep hidden.
The ID number is what I need to them be able to import the data into my Access database.
Is it possible to do this in Excel?

 

[Zathras]

Yes, with two hidden columns.

Say, for example that J and K are to be your hidden columns. Then set the following properties in the combo box:

LinkedCell J1
ListFillRange J2:J11

Then put your list of names in J2 thru J11,
put the corresponding numbers in K2 thru K11,
and put this formula in K1:

=VLOOKUP(J1,$H$2:$I$11,2,0)

When the user selects a name from the list, the corresponding number will be placed in K1. Then use a pushbutton to initiate the macro (or double click in the combo box).

Note 1: In your production version of the spreadsheet, it would probably be beneficial to use range names, I used cell references here to keep it simple.

Note 2: If you prefer, you could initialize the combo box in code and do the conversion from name to number in code as well, but setting combo box properties may be simplest.

 

[Rmcta]

Zathras you are GREAT! What you are suggesting me appears to be exactly what I want however I need some extra help.

On Each cell in column B (B2:B10)I am planning to put a combo box which value will be null as default.
When the users click on the cell (Example B2) they will pick a name from the combo box. As soon as they do that, I need Excel to insert the corresponding number on C2.

I don't know how to make your example work by using also the combo box.

Can you guide me?

Possibly I wish to create my 2 columns with the data on a different Sheet which will be hidden. It's name will be "Clients".

Waiting to hear from you I thank you in advance!

 

[Zathras]

Ok, now I'm confused.

What type of combo box are you referring to? I assumed (perhaps too rashly) that you were going to drop a combo box control from the Control Tool Box. That kind (as far as I know) simply "floats" on the worksheet and isn't actually "in" any particular cell. You can put a combo box "in" a cell by setting up Data/Validation... with a list. Is that what you are thinging of?

I don't understand the user interface of having a combo box in each cell in column B (B2:B10). Would they all have the same contents? If so, that is a very unusual interface. What exactly are you trying to accomplish.

Since you are ultimately importing into an Access database, why are you using Excel. Why can't you use a form in Access for your user interface.

I'll be happy to share any knowledge with you, but I can be of more service if you present a problem to be solved, rather than a solution to be implemented. Perhaps there is a better solution. By "better" I mean easier to implement, easier to maintain, or easier for the user to use (or all three).

 

[Rmcta]

It's Okay Zatras, I was able to make it work with what you suggested.
I am sorry for confusing you. I did not mean to.
Thank you very much for your help!