-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Updateing Changes to Vlookup Files
- Thread starter jeff007
- Start date
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.
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.
Upvote
0
Similar threads
- Question
