Excel - Two dropdown lists, two columns, two cells

[MSealy]

Hi,

I've been searching the net for an example of my problem, but alas I can't find it.

Basically I need to select a department name and related code number on a dropdown on a main page (Sheet1). To do this I need to be able to either select the name, or the code, on the two lists located in two cells next to eachother. These would reference the two columns of data in Sheet2. i.e

Sheet1
A1 (dropdown for Department), B1 (dropdown for Code) - selecting either, will display the correct data on the other.

Sheet2
A1 Accounts __________B1 1234
A2 Resources _________B2 0987
A3 Facilities __________ B3 1212
etc.

The data here would never change i.e. "Accounts" would always be "1234". So in Sheet1, if I selected "Accounts" from the dropdown, "1234" would appear in the dropdown next to it - and vice versa.

Once this was in place, any further forms I created, could reference cells A1 and B1 from Sheet1 to allow the current selection to appear on them.

I experimented with Data Validation at first, but limited to one column and finding no way to marry up two Data Validation sets, I gave up. Then I tried Comboboxes, then listboxes but still got stuck. Other examples online were not near enough to my problem and used reams of code.

I get the feeling this shouldn't be that difficult - it's just been a long time since I've tinkered with Excel VBA.

Can anyone help, thanks

Mark.

 

[SkipVought]

hi,

So you want to select either a DEPARTMENT and display a CODE or select a CODE and display a DEPARTMENT?

How about a simple INDEX() & MATCH() lookup -- no VBA required!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MSealy]

I've given that a go too, but after hitting my head against a brick wall for a few days on this, I think I'll just merge the two columns and make do with a Data Validation lookup on the single column.

Thanks anyway.

 

[SkipVought]

I've given that a go too, but ...

but WHAT?

You have not posted an example of what your tried, 'but'! Where is your formula???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MSealy]

Ok, this is as far as I've got:

In Sheet2, I've named the range A1:B3 as 'Costc'

In Sheet1, in cell A1 I've created a Data Validation as List, with the source "=Sheet2!A1:A3" i.e. the first column of 'Costc' in Sheet2. Still in Sheet1, in cell B1 I have the formula "=IF(A1="", "", VLOOKUP(A1, Costc, 2, FALSE))"

This now produces the correct 'Code' in B1, when I select the 'Department' in the dropdown list in A1. Half the battle won.

Now all I need is to perform this in reverse, so that a dropdown in B1 would result in the correct 'Description' in A1 - but here I run into the "VLOOKUP won't go left" issue. As for circular references I guess I could always create another set of these dropdowns, and hide one from each set - if that helps.

 

[SkipVought]

For this very reason, I very rarely use V or H lookups.

Rather I almost always use INDEX & MATCH, generically stated in my example in COLUMN RANGES, not TABLE RANGES (as you have in your example)...
[tt]
=INDEX([ReturnRange],MATCH([LookupValue],[LookupRange],0),1)
[/tt]
If you have Excel 2007+, then get acquainted with the features of Structured Tables. You will not need to use Named Ranges quite as much.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MSealy]

Thanks. Through that I've managed to make both parts of this work - but only separately.

I can get the results of the Department dropdown (Sheet1) from A1 in A2 using:
=INDEX(Sheet2!B1:B3,MATCH(A1,Sheet2!A1:A3,0))

I can get the results of the Code dropdown (Sheet1) from B1 in B2 using:
=INDEX(Sheet2!A1:A3,MATCH(B1,Sheet2!B1:B3,0))

However, I'm having to use A2 & B2 for these formulas instead of A1 & B1 as they will overwrite the dropdowns themselves in A1 & B1.

Now with a separate set of results in A2 & B2 (I could always white them out later), I'm trying to find a way of tying these in with the dropdowns but I'm going in circles trying to work this out. Is it possible to modify a dropdown list to obtain its source from the result of an INDEX MATCH lookup, whilst still giving the user a selectable choice from a column in Sheet2?