Data Validation List Matching Data From a Value of Another Cell

[nostrum]

Hi Guys,

Example attached, appreciate the help as always.

I have a table and I'm trying to create a data validation drop down list of all the products of a particular type (blue box / type) that would suit the application depending on a value (W) Any product with a value of W or above should be listed.

Hopefully that makes sense along with the example?

Thanks in advance

[63falcondude]

Try this:

I3 =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(Table2[Name])/((Table2[Type]=C$25)*(Table2[W]>B$25)),ROWS($1:1))),"")

Drag down as far as needed (I went to I15).

Then go to Formulas > Define Name > Name: DVname > Refers to:
=Sheet1!$I$3:INDEX(Sheet1!$I$3:$I$15,SUMPRODUCT(--(Sheet1!$I$3:$I$15<>"")))
OK

Now select cell D25 > Data > Data Validation > Allow: List > Source: =DVname > OK

See attachment.

[nostrum]

Thanks falcondude,

That's exactly what I'm after, the only problem is I would like the DV list to run horizontally across the columns rather than pull down, can you help with that?

Thanks!

[Pete_UK]

Here's a slightly different way, as I don't have the AGGREGATE function in my version of XL). Put this formula in H3:

=IF(AND(C3=$C$25,G3>$B$25),MAX(H$2:H2)+1,"-")

It should copy down the table automatically. You can change the column heading in H2 to seq, as that it what the formula does - produces a sequence of numbers for the records which match the criteria.

Then you can derive a table of the appropriate records. Put the label Valid_types in cell L7, and this formula in L8:

=IFERROR(INDEX(Table2[Name],MATCH(ROWS($1:1),Table2[seq],0)),"")

Copy this down as far as you need to. You can then define a named range Valid_types and you can make this dynamic in Name Manger by referring it to:

=Sheet1!$L$8:INDEX(Sheet1!$L$8:$L$20,COUNTIF(Sheet1!$L$8:$L$20,"?*"))

Then in D25 use Data Validation with a Source of =Valid_types.

Hope this helps.

Pete

[63falcondude]

I would like the DV list to run horizontally across the columns rather than pull down

Are you referring to the DV list source in I3:I15 or the actual drop down in cell D25?

[nostrum]

Are you referring to the DV list source in I3:I15?

Sure it's possible but this list isn't meant to be seen or even used for anything other than the DV in cell D25.

Yes, this list.

The problem I have is there are 20 lines where I need to create this data validation, so it makes sense to have each DV_List running in the same row as the corresponding data validation box? Otherwise, it could get a bit messy. I would then simply hide the columns that the lists were at unless you can think of a better idea?

Thanks again


Pete_UK - Just sussing your idea out..!

[Pete_UK]

Pete_UK - Just sussing your idea out..!

Here's the file, to make it a bit easier ...

Pete

[nostrum]

Thanks Pete,

That Might work. I can simply add 20 columns to my original table and refer to each column for every row I need?

[63falcondude]

Understood.

You can put this formula in I25:
=IFERROR(INDEX($B:$B,SMALL(IF((Table2[Type]=$C25)*(Table2[W]>$B25),ROW(Table2[Name])),COLUMNS($A:A))),"") Ctrl Shift Enter
Drag to the right (I went through column Z).

Then you can go to the Name Manager (in Formulas) and change the DVname formula to this:
=Sheet1!$I25:INDEX(Sheet1!$I25:$Z25,SUMPRODUCT(--(Sheet1!$I25:$Z25<>"")))
OK

See attachment.

[nostrum]

Hi Guys,

I'm using Pete_UKs method as it is easily scalable and works with the type of sheet i've created, thanks to you both for the input.

Similarly to the reason above, is it possible to change the valid_types array to work horizontally rather than verically at all? I can never understand how to do this
!

Many thanks

[Pete_UK]

Change the ROWS parameter to COLUMNS, like this:

=IFERROR(INDEX(Table2[Name],MATCH(COLUMNS($L:L),Table2[seq],0)),"")

then you can copy it across. You will also need to use Name manager to change the definition of the named range.

Hope this helps.

Pete

[nostrum]

Thanks Pete_UK this has worked out really well. I've managed to add extra conditions and learned a bit along the way!

[Pete_UK]

Well, that's good to hear - thanks for feeding back.

Pete