Dropdown - list of inventory items based on a sale date

[RickG75]

Hi All,

I am able to create a dropdown to select inventory items.

I would like to be able to limit the items in the dropdown list by a sale date indicated on the invoice.

Is this possible?

Link spreadsheet:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thanks,

Rick

 

[Toadstool]

Hi RickG75,

You can build your LoV in Inventory

Hillbilly Enterprises.xlsx
	A	B	C	D	E
1	Sale Date	Item	Retail Unit Cost		InventoryLoV
2	8/24/2020	Hoops - Green	$8.00		Hoops - Green
3	8/24/2020	Diamond Stud	$15.00		Diamond Stud
4	8/24/2020	Necklace - Blue	$7.95		Necklace - Blue
5	8/24/2020	Leather Belt - Black	$21.50		Leather Belt - Black
6	8/31/2020	Hoops - yellow	$6.75
7	8/31/2020	copper Stud	$13.56
8	8/31/2020	Necklace - red	$8.85
9	8/31/2020	Leather Belt - Brown	$18.26
Inventory

Cell Formulas
Range	Formula
E2:E9	E2	=IFERROR(INDEX($B$2:$B$9999,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/($A$2:$A$9999=Invoice!$A$14),ROW()-ROW($E$1))),"")

Then use OFFSET to offer the LoV

Hillbilly Enterprises.xlsx
	A	B	C	D	E	F
24	Item #	Description	UOM	Qty	Unit Price	Total Price
25	1	Diamond Stud	Ea		$15.00	$0.00
26	2	Leather Belt - Black	Ea		$21.50	$0.00
27	3		Ea
Invoice

Cell Formulas
Range	Formula
E25:E27	E25	=IF(ISBLANK(B25),"",VLOOKUP(B25,Inventory!$B$1:$C$19,2,0))
F25:F27	F25	=IF(ISBLANK(B25),"",D25*E25)

Cells with Data Validation
Cell	Allow	Criteria
D25:D27	List	=Inventory!$Q:$Q
B25:B27	List	=OFFSET(Inventory!$E$2,,,COUNTIF(Inventory!$E$2:$E$9999,"> "))

 

[RickG75]

Hi Toadstool,

Many thanks for taking your time to look into this and respond.

Maybe I should explain how I'd like to use this tool. I have a friend selling stuff on FaceBook. There are usually 50-60 items in a sale and typically 15-20 buyers. She would like to type in inventory based on a date she will sell all the items.

Then use the inventory and customer information she has gathered to build a tool to generate invoices instead of hand writing them.

The each dropdown will change based on what the customer purchased per invoice. I hoped there would be a way to write a formula into the data validation to limit the items showing up in the Description column (B25:b34) of the invoice based on the date on the invoice (A14).

This is why there is a Sale Date beside each Item on the Inventory tab. I thought this could be used to limit the items showing up on the invoice by date.

I hope this better understands what I am aiming at.

Thanks,

Rick

 

[Toadstool]

Hi Toadstool,
Many thanks for taking your time to look into this and respond.
Maybe I should explain how I'd like to use this tool. .....
...I hope this better understands what I am aiming at.
Thanks,
Rick

Yes, that's what the formulae do.
The Inventory formulae build a list of only items matching the Invoice Sale Date.
The Datalist then only offers an LoV in from B25 of those items using a List formula of
=OFFSET(Inventory!$E$2,,,COUNTIF(Inventory!$E$2:$E$9999,"> "))

 

[Peter_SSs]

Maybe I should explain how I'd like to use this tool. I have a friend selling stuff on FaceBook. There are usually 50-60 items in a sale and typically 15-20 buyers. She would like to type in inventory based on a date she will sell all the items.

1. Do you have the FILTER function in your Excel 365?
   
   
2. Does your friend also have Excel 365?
   If so, do they also have the FILTER function

 

[RickG75]

1. Do you have the FILTER function in your Excel 365?
   
   
2. Does your friend also have Excel 365?
   If so, do they also have the FILTER function

Hi Peter_SSs,

Yes, we both have Excel 365 and the filter button.

Thanks,
Rick

 

[Peter_SSs]

and the filter button.

I'm not asking about a filter button, but the FILTER() function.

If you click the fx button and choose 'Lookup & Reference' is FILTER in the list?

 

[RickG75]

Hi Peter,

My apologies, yes we both have the filter function.

Thanks,

Rick

 

[Peter_SSs]

yes we both have the filter function.

OK, thanks. This is how I would approach it.

On the Inventory tab, turn the data in columns A:C into a formal table (select anywhere in that data -> Ctrl+T -> My table has headers -> OK
Go to Formulas ribbon tab -> Name Manager -> Select this new table -> Edit... -> Give it a new name, say Inventory -> OK -> Close

On the invoice tab (if this is from a template you might do it there too)
In an unused column put the formula as shown in J25 below. The other results will 'spill' down the column. Column can now be hidden if you want.
Select B25:B34 and apply the very simple Data Validation formula shown below.

Now, as data is altered in the Inventory table or the date changed on an invoice the DV list will automatically update on each individual invoice sheet.

RickG75.xlsx
	A	B	C	D	E	F	G	H	I	J
13	Date:
14	08/24/20
15	Invoice No:
16	327000
17	Name:
18
19	Address:		Phone:
20
21	City, State, Zip:		Email:
22
23
24	Item #	Description	UOM	Qty	Unit Price	Total Price
25	1		Ea							Hoops - Green
26	2		Ea							Diamond Stud
27	3		Ea							Necklace - Blue
28	4		Ea							Leather Belt - Black
29	5		Ea
30	6		Ea
31	7		Ea
32	8		Ea
33	9		Ea
34	10		Ea
Invoice

Cell Formulas
Range	Formula
J25:J28	J25	=FILTER(Inventory[Item],Inventory[Sale Date]=A14)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B25:B34	List	=J$25#

 

[RickG75]

Hi Peter,

This is perfect! Exactly what I was hoping for.

I have 1 more question. I would like to make A14 on the Invoice tab a dropdown from the Sale Date from the Inventory tab. Is there a way remove the duplicate dates from the A14 dropdown?

Many thanks again,

Rick