Cascading Drop Downs

[imran999]

Hi,
I'm trying to create a stock inventory management sheet and unfortunatly am failing at my first hurdle.

I am trying to get cascading drop downs working in an excel sheet but am struggling to figure out how to have my drop down filtered on the results of a drop down to the left of it.

My Main table is the purchase sheet. All stock purchases are entered on here freehand. Which would then be detailed on sheet 2, using aid of drop downs based on sheet 1 data.

Work in progress.xlsx
	A	B	C	D	E	F	G
3	Supplier	Account Name	Order Date	Order ID	Order Value	Quantity
4	Nespresso	Account A	19/12/2023	66077465	£ 88.20	180
5	Nespresso	Account A	19/12/2023	66077466	£ 73.50	150
6	Nespresso	Account A	19/12/2023	66077467	£ 73.50	150
7	Nespresso	Account A	19/12/2023	66077468	£ 88.20	180
8	Costco	Account A	12/12/2023	123456	£ 120.00	100
9	Ebay	Account Z	15/12/2023	23564	£ 140.00	120
10	harveys	Account H	01/01/2024	4531	£ 18.00	140
11	asda	Account H	02/01/2024	155	£ 123.00	150
12	tesco	Account H	03/01/2024	115	£ 145.00	100
13	random	Account ABC	10/10/2024	ABC1234	£ 1,000.00	150
1. Purchase Sheet

I am then constructing a 2nd sheet (stock sheet testing), that i want to allow me to select from drop downs
a) unique set of suppliers
b) unique set of account names (dependent on the supplier chosen)
c) All unique order dates relevant to options chosen in A and B
d) All order ids based on drop downs A, B, C and D. The order id would always be unique.

Work in progress.xlsx
	A	B	C	D
2
3	Supplier	Account Name	Date	Order ID
4	tesco	filtered drop down	filtered drop down	filtered drop down
2. Stock Sheet Testing

Cells with Data Validation
Cell	Allow	Criteria
A4	List	=OFFSET('3. Working Calcs'!B3#,0,0, COUNTA('3. Working Calcs'!B3#),1)

My attempts so far are giving me the unique supplier list which grows as more data is entered.
To construct the other drop downs, i am a bit stumped. Been watching a few youtube videos but struggling to apply to my dataset.

Work in progress.xlsx
	B	C
2	Supplier	Account
3	asda	Account A
4	Costco	Account ABC
5	Ebay	Account H
6	harveys	Account Z
7	Nespresso
8	random
9	tesco
3. Working Calcs

Cell Formulas
Range	Formula
B3:B9	B3	=SORT(UNIQUE(FILTER(Table2[Supplier],Table2[Supplier]<>0)))
C3:C6	C3	=SORT(UNIQUE(FILTER(Table2[Account Name],Table2[Account Name]<>0)))
Dynamic array formulas.

Some guidance would be appreciated.

 

[alansidman]

Look at this link

 

[imran999]

Thanks, Working my way through the video.

I have implemented transpose which gives me the suppliers across the sheet....and then i have placed a forumula under the first supplier, which correctly gives me the account names for that supplier....
However when i copy across the formula to the adjacent cells under the transposed supplier list, the formula is shifting right.

Formula i have used in Cell F3

=SORT(FILTER(Table2[Account Name],Table2[Supplier]=F2))

When i copy it across to cells G3 it then shows as

=SORT(FILTER(Table2[Order Date],Table2[Account Name]=G2))

How do i stop the table columns shifting; Tried the $ before table name but it errors.?

 

[imran999]

I tried the following guidance, but gives me an error in my version of excel
Microsoft® Excel® for Microsoft 365 MSO (Version 2407 Build 16.0.17830.20166) 64-bit

Example:​

If your formula is =Table1[Column1] and you copy it across columns, it will automatically shift to =Table1[Column2] in the next column. To stop this from happening, use =Table1[@[Column1]]. This will keep the reference fixed to Column1 when you copy the formula across.