Updateing Changes to Vlookup Files

jeff007

New Member
Joined
Jul 7, 2006
Messages
27
I created an order entry system using a vlookup table and I just got the price hikes from the manufacturer, what is the best way to deal with this?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Welcome to MrExcel -- how do you mean?

Do you need to keep both old and new rates/prices, or ??
 

jeff007

New Member
Joined
Jul 7, 2006
Messages
27
Ok here is what I have, each of the below are individual worksheets, each have formulas entered into them that make the
Sales Receipt, and Purchase order populate with the customers order info automatically.

CustomerOrder
SalesReceipt
Purchase Order
Products

The CustomerOrder,SalesReceipt,Purchase Order all lookup info from the products table to complete entries in their respective worksheets.

Problem- price changes affect the customer order sheet for older orders using older prices. Since the customers order sheet is also used as a main
snap shot sheet to return various reports such as total sales by month,manufacturterm sales commsiions ect, all cells are programed to return info using the
current product list so once new product prices are entered old sales that remain on the customer order sheet are effected by using the new price instead of the old price at which they were sold.

Solution:

Make a new products sheet
.......A......................B....................C.......................D....................E......
1....Date..................................1/1/06...............2/1/06..............3/1/06
2....Item#........ Description....ProductPrice....ProductPrice.....ProductPrice
3....45689.........Widget Red.... $2695.00........ $2795.00.......... $2895.00


I need a formula that I can enter on the customerorder sheet that will lookup the order date from the customersorder sheet
and then lookup the product in the product sheet and return the price that correlates to the product price at the time the order was entered. Right now it just takes entry 5 from my products list to return a price, the formula in one cell that returns the price off product 1 currently
looks like this:

=IF(AB858=0,0,VLOOKUP(AC858,PRODUCTS2!$A$1:$G$20122,5,FALSE))*AB858

How do I make an excel formula that looks at cell B858 (this is the cell containing the date of the order in my customerorder sheet)
and selects the correct price from the products list? Your advice is greatly appreciated.
 

Forum statistics

Threads
1,136,346
Messages
5,675,229
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top