Help with V look-up

[nygwnj]

I made a template for a co-worker to use while I'm out of the office. It's for wholesale orders and he doesn't have access to our accounting software and can't make an invoice to send with the package. he would use the Excel file to make a packing slip.

I attached the order form (pdf and docx - doesn't matter) and the Excel file. The Excel file already has a sum function for the total price. the idea is that the item column would contain a drop down list of all the items in the order form. the retail and discounted wholesale columns would then autopopulate. my coworker would then apply a discount (varies depending on customer) to each line item.

Can you tell me how to:

1. create the drop down list of items in the item column
2. use v look-up (if that is the correct structure) to draw in the price for each particular item and insert it in to the retail price column

Thanks, again,

Gary Willick

[mikeTRON]

1) I do not see any items in the item column.
2) Where is the pricing DATA in excel to lookup from?

These should be fairly easy once you get those two items in the sample.

[nygwnj]

I had attached two order forms, one pdf, on docx. they contain the items and prices. Were the attachments not visible?

Gary

[mikeTRON]

Ah, the attachments are visible and I guess you are not aware if you want to do a vlookup OR a data validation list you have to have the data in excel.

[nygwnj]

Correct. I don't know the difference.

I replied to the email I received to gmail then got a "no reply" message back from site admin.

Gary

[newdoverman]

I have created a simple example for the addressing section to fill in the Billing and Shipping addresses from the name entered. The data for the names is on the Data worksheet and the drop down list for the name on the main worksheet is defined in Data Validation.

Using the same principles, you can create vlookups to fill in the orders if you create a table of the items.

[mikeTRON]

Correct. I don't know the difference.

I replied to the email I received to gmail then got a "no reply" message back from site admin.

Gary

You have to have the data in excel to do lookups. Your request should be fairly simple once you get the data IN excel.

[Cdubisms]

I've given it a shot.

See attached (Miketron, check it out too to make sure I didn't miss anything)

[newdoverman]

In your last example, the vlookup is working fine. Add this to F9 and fill down

Please Login or Register  to view this content.

If the formula for Data Validation is changed to the following, it will become dynamic (new data automatically added to drop down list)

Please Login or Register  to view this content.

The example that I gave you for the customers in my previous post can be implemented to fill in the customer data.

[Cdubisms]

I didn't want to mess with F9:F24 because I was unsure if they would be entering discounts in the Wholesale Rate column that would affect the price.

[Cdubisms]

I didn't want to mess with F9:F24 because I was unsure if they would be entering discounts in the Wholesale Rate column that would affect the price.

[newdoverman]

If/when discounts come into the picture, one can make changes then. Any discount would likely be based on volume of purchase but that isn't known at present.

[nygwnj]

Thank you.

I can't find your sample file.

Gary

[newdoverman]

I'm not sure who you are referring to. If you are referring to my message #9 I thought that it was your file but it belongs to Cdubisms. I suggested a couple of changes to the formula and to the conditional formatting.

[nygwnj]

Guru,

Thanks for editing the packing slip file and adding the data worksheet. And sorry I took a while responding.

I want the drop down items to be for the items and their retail price, not for the customers. So, I copied your Data worksheet and edited it so it contains just that - items and prices. But I don't know how to link that to Sheet 1 so the data is pulled in from a drop down.

There are too many customers to have a customer table. but I have about 30 items total. I attached the new file.

Gary

[newdoverman]

Sorry for the delay. This might help.

[nygwnj]

I figured it out. I used the help screen to see how to insert data validation as well as vlookup.

Gary

[nygwnj]

I have a working spreadsheet, but the problem is deleting cells. The column with the vlookup formula is giving me difficulty. If the user enters unnecessary data and wants to delete it, pressing delete deletes the formula too. if the user then realizes something else should go in that row, the formula is gone.

Do I need a reset button at the end of the row that deletes data only?
is there some menu option for a delete that deletes only data?

Gary

[newdoverman]

Most of the row is dependant upon the entry in column A. Just delete the contents of the value in column A and delete the quantity in column C. To eliminate the errors then produced, change the formula in E9 to the following and fill down:

Please Login or Register  to view this content.

In F9 enter and fill down

Please Login or Register  to view this content.

In G9 enter and fill down

Please Login or Register  to view this content.

This will leave you a blank row with the formulae intact.

	A	B	C	D	E	F	G
8	TC Item		Quantity		Retail Price	Wholesale Rate	Price
9	Catskill Trails		20		$ 16.95	$ 7.63	$ 152.55
10
11	Walkable Westchester		6		$ 24.95	$ 11.23	$ 67.37
12
13