Data Validation and Index Match

[TDC21]

Hello Everyone, sorry if this is a repeat, my first post kicked back for the registration confirmation and I am not sure if it was submitted or not.

I am trying to create a drop down list that is dependent on another cells data. I have a workbook that includes a worksheet linked to our Purchase Order Table, and I have a second worksheet that I am attempting to use as a data entry form. In the data entry sheet I have created a drop down list that validates the PO entry, but I would like to create another drop down list for the Part ID that limits the list values to only those that match the Purchase Order Table and the selected entry. I have tried using some Index Match array formulas as the source in data validation but with no luck.

Purchase Order Table

[TABLE="width: 482"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ROWID[/TD]
[TD]VENDOR_ID[/TD]
[TD]PO[/TD]
[TD]LINE[/TD]
[TD]PART_ID[/TD]
[/TR]
[TR]
[TD]153833[/TD]
[TD]RANSTE[/TD]
[TD]35706A[/TD]
[TD]1[/TD]
[TD]03750SA517B[/TD]
[/TR]
[TR]
[TD]153834[/TD]
[TD]RANSTE[/TD]
[TD]35706A[/TD]
[TD]2[/TD]
[TD]05000SB575-N06022[/TD]
[/TR]
[TR]
[TD]153835[/TD]
[TD]RANSTE[/TD]
[TD]35706A[/TD]
[TD]3[/TD]
[TD]03750SB171C71500[/TD]
[/TR]
[TR]
[TD]153832[/TD]
[TD]AMALST[/TD]
[TD]35705A[/TD]
[TD]1[/TD]
[TD]08750SA387112
[/TD]
[/TR]
</tbody>[/TABLE]


Data Entry Worksheet

[TABLE="width: 462"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]INVOICE[/TD]
[TD]PO[/TD]
[TD]PART ID[/TD]
[TD]QTY[/TD]
[TD]COST[/TD]
[/TR]
[TR]
[TD]Data Entry[/TD]
[TD]Validate[/TD]
[TD]Validate/Match[/TD]
[TD]Data Entry[/TD]
[TD]Data Entry[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible?

 

[Scott T]

This video explains how to due dependent data validation.

https://youtu.be/-78bH0RZ7MA

 

[Kamolga]

If you do not want to use macro to set up the range of your dropdown, the easiest way to have a dynamic dropdown is to list the caracteristic in a table and name its content (without the title). The dropdown list will vary according to what is in the table. It is then possible to refer to a name using indirect function.

Let's say that your table is a vlookup (or index match) of your ID and you name it IDlist (through formula tab, define name), then in your dropdox you simply type =IDlist and your list will vary accordingly

 

[TDC21]

Thanks Kamolga, thats not quite what I am trying to do though. The worksheet its self is not using any V-Look up or Index/Match, at least not with the Part ID's or PO #'s. With these fields I am just trying to validate the data so that manual entries will match system entries. The reference to Index / Match was that was my initial thinking about how to generate a source formula to limit the list values for the Part ID entry.

Also I did try naming the table range but it would not take, I could name the whole column or individual cells in the column but when I tried the entire table list it did not work. Any thoughts on that?

 

[TDC21]

I dont think the named ranges will work for me. I am trying create the drop down list by matching the Part ID's to a PO # number from the PO line table. For the named range to work I would need stable columns to create the validation fields correct? The PO#'s are not Column headers, they are row values that are constantly being updated so I cant just pivot the data to format that way either. Unless I am thinking of this incorrectly I need a validation formula that lists all instances of PO_LINE[PART_ID] were PO_LINE[PO#] = INVOICE_WS[PO#]. Unless I am thinking about this incorrectly, I think I have to find a table reference formula for the validation source.