Suggest Values by LOOKUP

[dhdevans]

I've got an inventory management system that could do with some slightly more intelligent design.

The order sheet generates requests e.g. Customer A needs Blue Boxes, Customer B needs Red Boxes, Customer C needs Red Boxes and Blue Boxes

The inventory sheet generates the boxes e.g. Blue Boxes, Red Boxes

At the moment each order has a drop down list that references the inventory sheet e.g.

Customer A

Boxes Required: Red

On click the dropdown menu will show Blue Boxes and Red Boxes, despite Customer A only requiring red boxes.

I want to restrict the values in the dropdown box to only what is required by the order e.g.

Customer A:

Boxes Required: Red

Dropdown menu: Red Boxes

Should i put something in the 'data validity' section of the dropdown menu so it will only show values that match the 'boxes required' column? If so, how do i even go about accomplishing that?

[Markus_Malessa · Accepted solution]

I would suggest using the 'Suggested Values' for this with a SELECT():

SELECT(BoxesTable[KeyColumn_or_OtherColumnToSelect], [BoxColor] = [_THISROW].[Boxes Required])

I'm just using made up names for column names, do not literally interpret this.

[dhdevans]

I suspected it would be something like this! I just couldn't quite get my head around it.