How to find the value besides the selected option.

[spyrule]

Hello,

I have a combobox list, that is populated from a column of data on a worksheet. Using the _change even with the combobox, how do I find the value of the column to the right of the selected option in the combobox. The value being on the right of the list on the worksheet.

This is what I have so far, but it doesn't work.

Please Login or Register  to view this content.

As you can guess the combobox lists all agents, and when an agent is selected their agent ID is shown. the agent's names are taken from column E, and the corresponding agent ID is column F :

E | F
------------------
agent 1 | 1
agent 2 | 2
agent 3 | 3

etc...

For some reason, I know this is simple, but as per me, I know I am over
complicating it.... it's a curse for me.

Any help with this is apreciated.

spyrule

[jtp]

Hi Spy,
An option would be to load both columns of data into your combobox. This would eliminate the need to search once the value was selected. To set that up you could use something like this.

Please Login or Register  to view this content.

And to read the value selected

Please Login or Register  to view this content.

HTH

[spyrule]

Hey,

thanks for that, however that doesn't really help. I would like to know the
Agent ID in a seperate variable. I use it in a few other locations, and this is
where I would like to fill it.

For some reason, I know this is totally possible, without using a multi-column
combobox.

The thing about this, is that the agent list is a somewhat dynamic list, and agents can be added to the list at any time (everytime we hire new agents, as a matter of fact). So I cannot hardcode a simple if statement to do it.

Any suggestions in a way to do this is apreciated.

spyrule

[jtp]

You can certainly run a lookup after the value is selected in the combobox. This to me is just the most efficient way however, I could be wrong. If your list grows, use the following.

Please Login or Register  to view this content.

And to assign the agent ID number to the value in the 2nd column:

Please Login or Register  to view this content.

[jtp]

Your way

Please Login or Register  to view this content.

[spyrule]

Hey,

Thank you VERY VERY much jtp.

It really is apreciated. Honestly knowing this function helps me in like 5 other
places as well.

Cheers'n'happy hollidays (little early, but hey it's the thought right!).

spyrule.

[jtp]

Happy to help