Data Validation with VLookUp

[boehnli01]

Hello - I have the following sitation:

xls Sheet 2
ITEM EQUIPMENT
1234 a
1234 b
5678 c
5678 d

xls Sheet 1
PO ITEM EQUIPMENT
56 1234 **
99 5678 **

** in the equipment cell of sheeet 1 I want a drop down (data validation) with the possible equipment I can use forthis item only. I.e. for PO 56 Item 1234 only the equipment a & b should be in the drop down menu.

Could somebody pls help me.

Thanks.

[Miraun]

It will likely require an additional reference sheet to assist you.

I could give you some more specific guidance, but here's a website that explains it in more detail than I'd be able to:

http://tim.webanalyticsdemystified.com/?p=1302

[boehnli01]

See enclosed example in xls.

[Hawkeye16]

I think this is what you are looking for. It will only work if the list on sheet 2 has all similar items next to each other as you have shown here. Easy to do with a sort if not already this way.

[Miraun]

See attached

[boehnli01]

Excactly. Both solutions are perfect.
Hawkeye's is the more suitable for me because my Equipment list is top-down. Or may be the same can be done with Miraun's solution ?
Could you pls explain me in details how you did that ?

Thanks.

[Hawkeye16]

We both used similar OFFSET formulas. Mine will be more robust as miraun assumes there is always 3 options exactly where mine will count the number of options and present them no matter the number (>0 obviously).

I put my offset formula right into the data validation formula while miraun created a defined name in the name manager with the formula and referenced it in the data validation.

The offset formula takes a target cell and you can specify a row offset, column offset, number of rows and number of columns (resulting in a possible array of values).

I started with an anchor cell and then found the row to start with using a Match function. I then also found the number of options (number of rows in the array) using a Countif function.

Miraun rearranged your data so they used a Match function to find the column offset.

[newdoverman]

Here is a solution that uses a table for all your data - Item and associated Equipment. You can add items just by adding a column to the table and under the heading enter the items that apply and the Data Validation is automatically updated.

[boehnli01]

First of all many thanks for the solutions. Just realized that I have a another file with a different setup. Basically the same logic but the equipment name (A, B, C etc) in the Data Validation is defined by a Y in Sheet 2. See enclosed

Is there a similar solution ? In advance thanks for your help.

[newdoverman]

This shows the value of the table solution that I gave you. Just add the 1010 in the heading area like I did and the values under it.

[newdoverman]

All I did was to add the column 1010 just by typing 1010 in the next column to the table and then entered the values for 1010 under it. The 1010 was added to the drop down for the Item, and was also added to the equipment drop down...automatically.

[boehnli01]

Thanks Newdoverman - I tried to apply your formulas, but struggled to follow in my file. I have enclosed again an updated filed. Would you mind to do the formulas again in this file and explain me how you did. Appologize for the confusion. Thanks.

[newdoverman]

Here is your file back with the drop down list working. I had trouble with your workbook in that there was formatting that messed up the operation of the formulae. In the end I got it fixed without really knowing where the problem was...lucky I guess.