Drop Down & V Lookup

[Chantelle]

Still on my restaurant menu database and I can think of what I want it do but
am unsure of what the commands are in excel!
I have one dependent list which looks up the category and then returns only
the ingredients from that category. Now I would like a second dependent
lookup that looks up the category and returns only the suppliers that supply
the products in that category. Currently it keeps returning the first
dependency. Any suggestions?
Chantelle

 

[Arvi Laanemets]

Hi

The easyest way:

Create a sheet Suppliers, where column headers are your categories, and in
every category column are listed suppliers for this category. P.e. from A1:
No Category1 Category2 Category3
Supplier1 Supplier1 Supplier1
Supplier2 Supplier3 Supplier2
Supplier3 Supplier4
Supplier5
, where into cell A2 you enter the formula
=IF(COUNTA($B2:$AX2)>0,ROW()-1,"")

Define named range
Categories=OFFSET(Suppliers!$B$1,,,1,COUNTA(Suppliers!$B$11:$AX$1))
(you use this named range to create your category selection drop-down,
i.e. )

Define named range
Suppliers=OFFSET(Suppliers!$A$1,1,1,MAX(Suppliers!$A:$A),COUNTA(Categories))

Activate the sheet with your main data. Let's assume the sheet has name
Data, and you have category in column A and Supplier in column B, with row
no 1 as header row. Select cell B2, and now define a named range
CatSuppl=OFFSET(Suppliers!$A$1,1,MATCH(Data!$A2,Categories,0),COUNTA(INDEX(Suppliers,,MATCH(Data!$A2,Categories,0)),1))

Now create drop-downs for cells Data!A2 and Data!B2, using named ranges
Categories and CatSuppl respectively, and copy both cells down for as much
rows as you need.

 

[Chantelle]

Thanks Arvi,

I have created a sheet suppliers and completed the first step, but then I am
confused by the following

a)IF formula - where does this go exactly in A2 exactly & why as this is an
example of waht my spreadsheet looks like.

Col A Col B
Col C
1 Alcohol Dairy
Dry Goods
2 Dromana Cellars Calender Cheese Fastrac
3 Imports of France Fastrac Imports
of France
4 Peninsula Dairies
The Essential Ingredient

b) The define name range is this in Insert/Name/Define and then input this
formula section? and the Suppliers!$B$1,,1,is that picking up the information
in my A1 above?
c) I am really confused with the last bit.

sorry this is my first time using dynamic drop downs so I need a bit of drop
down for dummies.

Thank you
Chantelle

 

[Arvi Laanemets]

Hi

Thanks Arvi,

I have created a sheet suppliers and completed the first step, but then I
am
confused by the following

a)IF formula - where does this go exactly in A2 exactly & why as this is
an
example of waht my spreadsheet looks like.

The formula goes into cell A2 on sheet Suppliers and you copy it down at
least for as much rows as max number of suppliers in any category. It
enumerates rows with at least one supplier in, so you enter only suppliers
into columns starting from column B - the column A is calculated
automatically. NB!. Categories start from column B, Column A is reserved for
a counter (in my example it has header No) - without it named range
definitions wii be much more complex.

Col A Col B
Col C
1 Alcohol Dairy
Dry Goods
2 Dromana Cellars Calender Cheese Fastrac
3 Imports of France Fastrac
Imports
of France
4 Peninsula Dairies
The Essential Ingredient

b) The define name range is this in Insert/Name/Define and then input this
formula section? and the Suppliers!$B$1,,1,is that picking up the
information
in my A1 above?

Exactly! And the named range returns the list of categories from range B1
and to right of it on sheet Suppliers.

c) I am really confused with the last bit.

Which one?

sorry this is my first time using dynamic drop downs so I need a bit of
drop
down for dummies.

No problem there at all )

 

[Arvi Laanemets]

Hi again!

I have to emphasize, that when defining the named range CatSuppl, you must
activate cell Data!B2 (really any cell in 2nd row of any sheet, but it will
be easier this way tu understand the logic). And be careful to leave row
reference in Data!$A2 relative - the named range CatSuppl is dynamic in 2
different ways, it depends on category value on same row in sheet Data, and
on number of suppliers in according Category column in Suppliers sheet.


And there was a typo in Categories named range definition. It must be
Categories=OFFSET(Suppliers!$B$1,,,1,COUNTA(Suppliers!$B$1:$AX$1))