Variation to pulldown lists and populating data

[krt777]

Need a little help with this problem that is kind of related to this same problem but a little different.

I want both the fruit and code to have data validation lists.

The fruit is unique and the code associated with the fruit is also unique.

A user should be able to pull down and select a fruit and the correct code would be populated.

A user who knows the code could pull down the code and the correct fruit would be selected.

I select 10147 for Larry and the Fruit ends up Banana.

I select Pears for Mike and the Code ends up as 10148.

There is a two way dependency between the cells.

Can anyone help with how to do this?

[mrice]

Try the attached workbook.

It uses a macro driven by a worksheet change event to do the matching. You can see the code by hitting Alt F11 and going to the Sheet1 tab.

[krt777]

Thanks for your help Martin! I don't know VB but understand programming so I think I know what you are doing. A couple questions.

I'm assuming the Changing boolean is used as a semaphore?

My lists and orders will be on different worksheets. How do you specify in VB the worksheet? Is the sheet specified in the Range?

Target.Offset(0, 1) = Range("A2:A6").Find(Target, , xlValues, xlWhole).Offset(0, 1)

Like programing there are many ways to solve a problem. Is there a way to do it without VB?

[mrice]

The Boolean is a mechanism to avoid a stack of calls. When the looked up value is placed in the adjacent cell, this generates its own change event which calls the same piece of code. If the Boolean wasn't there, you would get a ping-pong effect and very quickly run out of stack space.

To specify the sheet use..

Please Login or Register  to view this content.

I can't thing of an obvious way of doing this without VB as if you used formulae, this would get into circular referencing. I'm happy to be proved wrong!