Cascading validation

[kieranm105]

I have a stock purchase order sheet that I want to make easy for the people in the office to use.
There is a sheet with all the cost tables on ('Costs'), a sheet with all the names and addresses etc of the suppliers ('Supplier List') and then the order form itself ('1').
On the order sheet I have a drop down box that selects the supplier and adds their details to the cells below it.
There is then a column that I want to have drop downs in showing only the codes that correspond with the selected supplier. When a code is picked, I then want the description and cost cells to be automatically filled in. This leaves the Qty column to be completed by the user. There are tables and names defined on the workbook for all the items.
I have looked around the net and tried a few of the examples but nothing seems to work.

Can anyone have a look and point me in the right direction?
Kieran

[JohnTopley]

For each set of Supplier codes, create a named range:

e.g. Addington_Engineering_Code (note the underscores in the name)

In A23 down

Set Data validation

Allow: =List

Source: ==INDIRECT(SUBSTITUTE($G$8," ","_") &"_Code")

This assumes your named ranges are the supplier name suffixed by "_Code"

In B23

=VLOOKUP($A23,Costs!$M$4:$O$25,2,0)

or

=VLOOKUP($A23,AddingfordTable,2,0)

To be more generic, named ranges could (should) be based on full supplier names



in J23

=VLOOKUP($A23,Costs!$M$4:$O$25,3,0)

or
=VLOOKUP($A23,AddingfordTable,3,0)

[dflak]

I did this using a series of helper columns that result in a rather hairy Offset command.

I demonstrate the concept on Sheet1. This is where you select the manufacturer in cell A9. The item description can be selected in cell A11.

My objective is to use the offset command. The following is a pseudo formula of what I want to do.

=OFFSET(Costs!$A$1,3,X,COUNTIF(Y:Y)-2,1)

I start in cell A1

I go down three rows (this is fixed).

X is the number of columns to go over. To find where the manufacturer in Cell A9 happens, use =MATCH(Sheet1!$A$9,Costs!$1:$1,0).

Subtract one since match counts from 1 and offset counts from zero.

Now comes the tricky part. Y:Y is the column containing the item. We have to tell the offset command what this column is. Fortunately we know its offset from column A. Namely: MATCH(Sheet1!$A$9,Costs!$1:$1,0) -1.

So to identify this column we use OFFSET(Costs!$A:$A,0, MATCH(Sheet1!$A$9,Costs!$1:$1,0) -1.). We can use this in the place of the placeholder Y:Y.

Now that we have the pieces, we can substitute them back in to the pseudo formula we started with:
=OFFSET(Costs!$A$1,3, MATCH(Sheet1!$A$9,Costs!$1:$1,0)-1,COUNTIF(OFFSET(Costs!$A:$A,0, MATCH(Sheet1!$A$9,Costs!$1:$1,0) -1.))-2,1).

If I did the substitution right, I should wind up with this formula:
=OFFSET(Costs!$A$1,3,MATCH(Sheet1!$A$9,Costs!$1:$1,0)-1,COUNTA(OFFSET(Costs!$A:$A,0,MATCH(Sheet1!$A$9,Costs!$1:$1,0)))-2,1)

To get code and cost, you can use:

My_Code = OFFSET(My_Item, 0,-1)
My_Cost = OFFSET(MY_Item, 0, 1)

No VB code required.

[kieranm105]

Thanks for the replies, I will try both options.
Looking at the first option JohnTopley:
I have defined the names & tables to match the ones you added on for the remainder of the suppliers that I have the costs for.
The formulas all work if I have them in separate cells. How do I make one long formula to look up the details?

Individual formulae:
=VLOOKUP($A23,Masterflex,2,0)
=VLOOKUP($A23,LindabTable,2,0)
=VLOOKUP($A23,CommercialNamplateTable,2,0)
=VLOOKUP($A23,AddingfordTable,2,0)
=VLOOKUP($A23,BarrettTable,2,0)

This doesn't give me any errors, but also doesn't work so I am missing something. I've tried a few variations (commas in and OR).

=VLOOKUP($A23,Masterflex,2,0)=VLOOKUP($A23,LindabTable,2,0)=VLOOKUP($A23,CommercialNamplateTable,2,0)=VLOOKUP($A23,AddingfordTable,2,0)=VLOOKUP($A23,BarrettTable,2,0)

[JohnTopley]

You need to choose your "lookup" table depending on the supplier name as per my first post.

To be more generic, named ranges could (should) be based on full supplier names

so for exmple

=VLOOKUP($A23,AddingfordTable,2,0)

would become

=VLOOKUP($A23,Addingford_Engineering_Table,2,0)

With supplier name in G8

then you could use

=VLOOKUP($A23,substitute(G8," ",_") & "_Table",2,0)

Any alternative is to have a table with the full supplier names and their shortened version:

Addingford Engineering/AddingfordTable
Barrett Precision Tube/BarrettTable


Put this table in "Supplier Lists", say columns X & Y

in (for example) M8 of Tab "1":

=VLOOKUP(G8,"Supplier Lists'!X2:Y50,2,0) to get table name

then Lookup for Item Description etc


=VLOOKUP($A23,INDIRECT(M8),2,0)

Hope this helps

[kieranm105]

I've changed the table names to their full names and added the formula:
=VLOOKUP($A23,substitute(G8," ",_") & "_Table",2,0)

This gets an error and won't let me off the cell, so I changed it to this:
=VLOOKUP($A23,substitute(G8," ","_") & "_Table",2,0)
Now I get a #Value error?

[JohnTopley]

In B23 and drag down

=IFERROR(VLOOKUP($A23,INDIRECT(SUBSTITUTE($G$8," ","_")& "_Table"),2,0),"")

in J23 and drag down

=IFERROR(VLOOKUP($A23,INDIRECT(SUBSTITUTE($G$8," ","_")& "_Table"),3,0),"")

This reverts to the table with FULL Supplier name.

in K23 and drag down

=IF(A23="","",$I23*$J23)

If this OK, you can remove the "shortened" table names.

[JohnTopley]

Another possible approach (not coded / untested as yet!) is to create one large table with ALL suppliers in it then dynamically create a "lookup" table for the selected supplier.

This removes the need to all the named ranges.

Supplier table would have the following:

Supplier Name
Item Code
Item Description
Item cost

[kieranm105]

That's exactly what I want thanks.

[JohnTopley]

This is the other approach:

Cell G8 in "1" is named "Order_Supplier"

Tab "Suppliers" has two tables

A:D is Master list of all Suppliers

G:J is data for supplier selected in G8 so when G8 is changed a new table is generated.

Formula G2:

=IFERROR(INDEX(Supplier_Master,SMALL(IF($A$2:$A$501=Order_Supplier,ROW($A$2:$A$501)-ROW($A$2)+1,""),ROWS($A$2:$A2)),COLUMNS($A:A)),"")

Enter with Ctrl+Shift+Enter

Copy across and down (as far as likely maximum from one supplier)

Column H is named "Item_Code"

Data Validation in A23 of "1"

=Item_Code

in B23

=IFERROR(VLOOKUP($A23,Supplier_Items,2,0),"")

in J23

=IFERROR(VLOOKUP($A23,Supplier_Items,3,0),"")

"Supplier_Items" is named range for columns H;J of the dynamic table.