From Dropdown, pull data to a table from another worksheet

[Mack23]

Hello,

Currently working on a workbook that has inventory allocation on one sheet and another sheet that is going to pull data and make an order sheet for the customer. On the order sheet, I have a table with a dropdown that is selecting the customer. Once a customer is selected, then I would like to be able to fill in the line items from the allocation sheet, on consecutive lines (old sheet had a row per product that used VLOOKUP's to pull the product information) so there are no blank row(s) between each allocated product on the order sheet.

The way I am thinking about making the macro is to first find the customer on the allocation sheet from the selected customer drop down on Order sheet. Second find all the cells with values in the column of that customer in the allocation sheet>copy and pasting into the order sheet on the respect rows. Third would be to make sure there are enough lines on the order sheet before copying a line. I can visualize what the macro will need to do, just have been not successful with the second part or combining all three parts together. any help is appreciated, even if it is part of the solution (inserting rows, finding customer, copying data). I feel like this is very complicated, but the last time I asked for help on this forum the solution wiped my 10 line macro for a 1 line code....

Thank you,
Mack

Allocation Sheet

Order Sheet

 

[RasGhul]

Hi Mack,

Do you prefer VBA or is formula ok?

 

[Mack23]

Hi Mack,

Do you prefer VBA or is formula ok?

I don't know if a formula will entirely work for what is needed, especially for the case of inserting a row if the table fills up with more than the shown rows.

The old Order sheet used vlookups but it made a "sawtooth" (blank rows) in the table between products that the customer was ordering. Then I did try a INDEX/MATCH type formula, but that was not working any better than the VLOOKUP (just a little cleaner in logic to me).

 

[RasGhul]

You could use this? I'm staging the allocation data with named ranges which changes with the drop down.

Expand the ranges as required for your order size, the staging area will still work if hidden.

Pull data from Dropdown.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Customer	A-1002	A-1023	A-1020	B-0233	B-0344	B-0259	C-0800	C-0821	C-0843
2	Batch #	Description	SKU	Total
3	J20442	STEEL	ROD	1000	200	200		400				200
4	H23343	STEEL	TUBE	2000		200	500	100		250		250	700
5	E33432	ALUMINUM	TUBE	3000	600		400	100	250	450	750	100	350
6	J55032	TIN	ROD	800		100			250		150		300
7	J55402	TIN	WIRE	800		150	150			200		300
8	H123405	COPPER	WIRE	1200	500			700
9	E44531	COPPER	TUBE	2700	500	400	250		450		500		600
Allocation

Pull data from Dropdown.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		CUSTOMER	A-1023
3
4					Total
5
6	Batch #	Description	SKU	Quantity	List Price	Line Total		Batch #	Description	SKU	Quantity	Rows
7	J20442	STEEL	ROD	200				J20442	STEEL	ROD	200	1
8	H23343	STEEL	TUBE	200				H23343	STEEL	TUBE	200	1
9	J55032	TIN	ROD	100				E33432	ALUMINUM	TUBE	0	1
10	J55402	TIN	WIRE	150				J55032	TIN	ROD	100	1
11	E44531	COPPER	TUBE	400				J55402	TIN	WIRE	150	1
12								H123405	COPPER	WIRE	0	1
13								E44531	COPPER	TUBE	400	1
14											0	1
15											0	1
16											0	1
Order

Cell Formulas
Range	Formula
A7	A7	=IF(ROWS(A$7:A7)>COUNTIF($K$7#,">0"),"",INDEX(Batch,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(A$7:A7))))
B7	B7	=IF(ROWS(B$7:B7)>COUNTIF($K$7#,">0"),"",INDEX(Description,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(B$7:B7))))
C7	C7	=IF(ROWS(C$7:C7)>COUNTIF($K$7#,">0"),"",INDEX(SKU,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(C$7:C7))))
D7	D7	=IF(ROWS($D$7:D7)>COUNTIF($K$7#,">0"),"",INDEX($K$7#,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS($D$7:D7))))
H7:H13	H7	=Batch
I7:I13	I7	=Description
J7:J13	J7	=SKU
K7:K16	K7	=OFFSET(INDIRECT(CELL("address",INDEX(Allocation!$E$1:$M$1,MATCH($C$2,Allocation!$E$1:$M$1,0)))),2,,COUNT(L:L))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Batch	=Allocation!$A$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$A:$A)+1,1)	A7:C7, H7:J7, H8
Description	=Allocation!$B$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$B:$B)+1,2)	A7:C7, H7:J7, I8
SKU	=Allocation!$C$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$C:$C)+1,3)	A7:C7, H7:J7, J8

Cells with Data Validation
Cell	Allow	Criteria
C2:C4	List	=Allocation!$E$1:$M$1

 

[Mack23]

You could use this? I'm staging the allocation data with named ranges which changes with the drop down.

Expand the ranges as required for your order size, the staging area will still work if hidden.

Pull data from Dropdown.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Customer	A-1002	A-1023	A-1020	B-0233	B-0344	B-0259	C-0800	C-0821	C-0843
2	Batch #	Description	SKU	Total
3	J20442	STEEL	ROD	1000	200	200		400				200
4	H23343	STEEL	TUBE	2000		200	500	100		250		250	700
5	E33432	ALUMINUM	TUBE	3000	600		400	100	250	450	750	100	350
6	J55032	TIN	ROD	800		100			250		150		300
7	J55402	TIN	WIRE	800		150	150			200		300
8	H123405	COPPER	WIRE	1200	500			700
9	E44531	COPPER	TUBE	2700	500	400	250		450		500		600
Allocation

Pull data from Dropdown.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		CUSTOMER	A-1023
3
4					Total
5
6	Batch #	Description	SKU	Quantity	List Price	Line Total		Batch #	Description	SKU	Quantity	Rows
7	J20442	STEEL	ROD	200				J20442	STEEL	ROD	200	1
8	H23343	STEEL	TUBE	200				H23343	STEEL	TUBE	200	1
9	J55032	TIN	ROD	100				E33432	ALUMINUM	TUBE	0	1
10	J55402	TIN	WIRE	150				J55032	TIN	ROD	100	1
11	E44531	COPPER	TUBE	400				J55402	TIN	WIRE	150	1
12								H123405	COPPER	WIRE	0	1
13								E44531	COPPER	TUBE	400	1
14											0	1
15											0	1
16											0	1
Order

Cell Formulas
Range	Formula
A7	A7	=IF(ROWS(A$7:A7)>COUNTIF($K$7#,">0"),"",INDEX(Batch,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(A$7:A7))))
B7	B7	=IF(ROWS(B$7:B7)>COUNTIF($K$7#,">0"),"",INDEX(Description,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(B$7:B7))))
C7	C7	=IF(ROWS(C$7:C7)>COUNTIF($K$7#,">0"),"",INDEX(SKU,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(C$7:C7))))
D7	D7	=IF(ROWS($D$7:D7)>COUNTIF($K$7#,">0"),"",INDEX($K$7#,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS($D$7:D7))))
H7:H13	H7	=Batch
I7:I13	I7	=Description
J7:J13	J7	=SKU
K7:K16	K7	=OFFSET(INDIRECT(CELL("address",INDEX(Allocation!$E$1:$M$1,MATCH($C$2,Allocation!$E$1:$M$1,0)))),2,,COUNT(L:L))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Batch	=Allocation!$A$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$A:$A)+1,1)	A7:C7, H7:J7, H8
Description	=Allocation!$B$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$B:$B)+1,2)	A7:C7, H7:J7, I8
SKU	=Allocation!$C$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$C:$C)+1,3)	A7:C7, H7:J7, J8

Cells with Data Validation
Cell	Allow	Criteria
C2:C4	List	=Allocation!$E$1:$M$1

Let me take a look at what you got and run some scenerio's to see how it will work. I don't fully understand your expand ranges comment for incresased order size as all your formulas seem to be row based not column based, so if we have one customer order 5 items and the next order 20, what has to change?

Thanks for your help, at first glance, this seems like it could work.

 

[RasGhul]

Thanks Mack

When your Customer Id numbers increase expand
=OFFSET(INDIRECT(CELL("address",INDEX(Allocation!$E$1:$M$1,MATCH($C$2,Allocation!$E$1:$M$1,0)))),2,,COUNT(L:L))

When your allocation Batch numbers increase
Add more 1 rows in Column L (Order Tab)
Drag down the order rows so that you return total Batch numbers possible
=IF(ROWS(A$7:A7)>COUNTIF($K$7#,">0"),"",INDEX(Batch,SMALL(IF(

 

[Mack23]

Thanks Mack

When your Customer Id numbers increase expand
=OFFSET(INDIRECT(CELL("address",INDEX(Allocation!$E$1:$M$1,MATCH($C$2,Allocation!$E$1:$M$1,0)))),2,,COUNT(L:L))

When your allocation Batch numbers increase
Add more 1 rows in Column L (Order Tab)
Drag down the order rows so that you return total Batch numbers possible
=IF(ROWS(A$7:A7)>COUNTIF($K$7#,">0"),"",INDEX(Batch,SMALL(IF(

Ok, so it is not fully working when I put it in place on the live sheet. The data columns worked where you pull the allocation data to the order sheet. On the order sheet portion, the Lot # and Quantity is not being populated (lot is returning Name? error and the quantity column is empty. Seems like the array formulas are working but not the regular formulas in column A and D.

 

[Mack23]

Ok, so it is not fully working when I put it in place on the live sheet. The data columns worked where you pull the allocation data to the order sheet. On the order sheet portion, the Lot # and Quantity is not being populated (lot is returning Name? error and the quantity column is empty. Seems like the array formulas are working but not the regular formulas in column A and D.

I got the quantity column to work on the SO table of the order sheet, still looking into why the #NAME? error is coming up in the Lot# column of the SO table. I will keep you updated and if you have an idea on why that formula would not be working, please let me know.

 

[RasGhul]

Sounds like Lot # is a new column & not the same as "Batch #"

e.g. =IF(ROWS(A$7:A7)>COUNTIF($K$7#,">0"),"",INDEX(Batche,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(A$7:A7))))

The Array will return #NAME? when there is no matching matching range, note in the above that Batche doesn't match the named range called Batch in my solution.

Batch

=Allocation!$A$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$A:$A)+1,1)

A7:C7, H7:J7, H8

What column is Lot #? you may need to create the named range.

 

[Mack23]

Sounds like Lot # is a new column & not the same as "Batch #"

e.g. =IF(ROWS(A$7:A7)>COUNTIF($K$7#,">0"),"",INDEX(Batche,SMALL(IF($K$7#>0,ROW($K$7#)-ROW($K$7)+1),ROWS(A$7:A7))))

The Array will return #NAME? when there is no matching matching range, note in the above that Batche doesn't match the named range called Batch in my solution.

Batch

=Allocation!$A$3:INDEX(Allocation!$1:$1000,COUNTA(Allocation!$A:$A)+1,1)

A7:C7, H7:J7, H8

What column is Lot #? you may need to create the named range.

Sorry, meant to say Batch # not Lot #. But that was it! the named range I had as Batch_# and the formula had Batch. Thank you, I believe it is working as need be. The only thing that isn't totally user hands off is having to insert more rows into the table if the customer orders more than what the table is built for, but this is a better option that what was in place before.

Thank you very much for your help.