08-21-2019 11:57 PM
08-21-2019 11:57 PM
I manage a spreadsheet of sales data.
we only have 5 products. but they keep changing the price.
I currently use a vlookup to get the correct price - that works fine. BUT if i change the price in the Vlookup all the old sales also change and i dont want that.
Sales after Sept 1 = $x but sales before Sept 1 =y
what is the best way to handle this?
08-22-2019 02:03 AM - edited 08-22-2019 02:07 AM
The easiest workaround is after placing the formulas, copy the formula cells (which actullay returns prices and you don't want them to change next time when you change the price) and paste them back as "Values and number format".
This is how you should paste the copied cells back as Values and number formatting.
Or after copying the formula cells, you may use the shortcut key Alt, E, S, U, Enter to paste them back as Values and number formatting.
Remember you can only use this shortcut key when the clipboard is not empty i.e. you have copied something.
Otherwise you may consider a Macro to insert the formula in selected cells and then convert them back as values so in the end the worksheet will not have any formulas and next time when you need to lookup the price for different set of cells, use the macro again to insert the formula.
08-22-2019 07:22 AMSolution
This is a common situation.
You may have multiple prices based upon different criteria (Say Store Regions, Clients Categories...etc)
In your question it is a DATE Cutoff (Before or After a specific date Say 1 September 2019
I created a sample File for you with 2 Price lists and in Cell B1 you enter the CutOff date
In the setup
we'll extract the price with a VLOOKUP function. However the Table array can be Price List 1 or 2 based upon the DateSo There is an IF function that selects the Table number (1 or 2 ) based upon the cutoff Date.
This if Function, is the first argument of a CHOOSE function: in case the IF returns "1" then the CHOOSE selects the first Price List and provides it as a Table Array to the VLOOKUP function. Same concept for dates after the CutOff date.
here is also a screenshot and the Function is:
I tested it and it is working fine
Of course the setup can be modified as needed with more Price Lists and more conditions
Hope that Helps