Excel function: Restricting list of products appearing on drop-down list

[JKLM34]

Project: I'm using Excel to create a receipt/invoice for buyers at a local high school auction. I have three pages in my excel workbook. First page: Buyer information (this will include their bid number), second page: products, third page: receipt/invoice. I'm using VLookup to grab the descriptions and price of each product to populate my invoice body. Is there a formula/function that can also populate the product number based on the buyer number that appears at the top of the invoice? I'd be ever so grateful... everything i've tried returns an error.

Because this is an auction, it's possible that the same buyer (bid number) could buy multiple items. Each product (only 1 qty) has a unique item number assigned. So, my product sheet is filled with 100s of items with their descriptions and prices. We won't know how many buyers we have until the evening of the auction, but when testing the receipt it's already a pain to scroll through the hundreds of product numbers when using the list option in data validation. We have student and elderly volunteers, so we need this to be simplified.

If that's wishful thinking, can you tell me how to limit the number of products that appear in the drop-down menu based on the bidder number so we're not combing through 100s of products to find the 3 or 4 items the buyer has won?

Your wisdom is greatly appreciated!! I'd attach screen shots, but being a new member I can't attach files. Hopefully you can visualize what I'm describing

 

[RasGhul]

Hi

Are you able to paste some sample data as this will help us come up with a solution for you

 

[JKLM34]

Product page same looks like this:

Sale #	Tag #	School	Consignor	Weight	Product	Buyer #	Price	Total
158	163	PCHS	Lindsay Dunkle	14.57	Choice Ham	65	8.70	126.76
159	164	PCHS	Lindsay Dunkle	14.35	Choice Ham	16	9.07	130.15
255	361	PHS	McCoy Fitzwater	19.00	Good Ham	65	10.20	193.80
91	213	MHS	Kristin Duncan	24.00	Choice Ham	30	11.00	264.00

<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Client Page sample looks like this:

Buyer #	Name	Street	City	State	Zipcode	Phone Number	Slicing Location
4	Susan Miller	2013 Excel Street	Chicago	IL	60611		Moorefield
5	Markwood Construction	testing testing					Carry Out
6	JR Keplinger	testing 1234					Pendleton
16	Amy Baker	2013 Microsoft Street	Miami	FL	33133		Moorefield
30	Brian Green	2010 VBA Road	New York	NY	10002		East Hardy
65	Caroline Martin	2007 Excel Road	Los Angeles	CA	87457		Pendleton

<colgroup><col><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

Invoice Looks like: (the client number calls the client information to fill the address. The slicing location is called for delivery of items to correct school for pickup) What I would like to happen is pasted below. The two orders that bidder number 65 won populate in the body below. Right now, I can only get the full list of products to populate.

			Date:	1/18/2019
Buyer:		Buyer #:	65
Caroline Martin
2007 Excel Road
Los Angeles
CA	87457
	Sale #	Description	Bid Price	Weight	Amount
	255	Good Ham	$ 10.20	19	$ 193.80
	159	Choice Ham	$ 9.07	14.35	$ 130.15
				Total	$ 323.95
				Cash
			Check #
	Slicing Location	Pendleton		Balance	$ 323.95

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[RasGhul]

Hi JK,

So the main goal would be to auto-populate the invoice with products as selected from the buyer number drop down.

I've done a quick mock up of 2 versions, ranges & tables. On the ranges one you'll need to maintain the ranges in the formulas as you add rows to either your Buyers or Product lists.

The Table version allows you to add rows to Buyer & Products without having to maintain your formulas as they will update automatically with a little more setup.



See if you can use these;

Ranges
https://www.dropbox.com/s/ekmpg70i7y2p4bl/Invoice fill Buyer Drop Down_ranges.xlsx?dl=0

Table
https://www.dropbox.com/s/glh3161bb83g5zt/Invoice fill Buyer Drop Down_Table.xlsx?dl=0

 

[JKLM34]

The Table version allows you to add rows to Buyer & Products without having to maintain your formulas as they will update automatically with a little more setup.

Tried them both. Leaning towards the table, though I'm not sure what you mean by 'with a little more setup'. I went ahead duplicated some of the buyer and product information to test the invoice. Looks great! I've also extended the invoice sheet to allow up to 10 items to populate on an invoice, which happened when I duplicated the information on the other sheets. No wonder I couldn't get the function to limit, I wasn't even close with my code. You're amazing! Thank you x infinity!

I noticed if I click on a cell with the formula it creates a #value remark, I would undo to remove it from the invoice page. Do you think I should lock down that section before turning it over to students? Or will that conflict with code?

 

[RasGhul]

You're welcome thanks for the feedback.

Sheet protection works ok with formulas but vba can be tricky. I would only lock down the formula cells.

You could also use this cool dynamic named range trick for the data validation on E2 so that the buyer numbers will be dynamic as new buyers are added or removed from your Buyer info sheet;

https://www.youtube.com/watch?v=2O3fvlBHLzE