Aug 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?
Aug 22 2019 02:03 AM - edited Aug 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.
Aug 22 2019 07:22 AM
SolutionHi
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:
=VLOOKUP($B5,CHOOSE(IF($A5<=$B$1,1,2),$G$5:$H$9,$G$14:$H$18),2,0)
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
Nabil Mourad
Aug 22 2019 04:55 PM
Jan 17 2022 04:55 AM
Mar 09 2022 09:16 PM
Hello,
Today I come across with same issue that if new price list has issued then the new price per item will apply to sales report from that date and onwards till another price list issued. Though I was eager attached file so that you may check full functionality but I got popup message that .xlsx file format not supported to this forum, following screenshots may help you:
As you can see I have created table of Price List on right and daily sales report on left column rows on column A automatically determine the applicable tariff by using formula:
=IFERROR(INDEX($H$3:$I$18,MATCH(B3,$I$3:$I$18,1),1),"")
Index function returns a value or the reference to a value from within a table and Match Function determines exact row to revert value from, match type = 1 determines that latest preceding value, from Tariff Table, of say value of Cell A3.
Sumifs function in "Rate" (column D) matches the multiple ifs to determine which row value from column K to revert as Rate of certain item.
Hope this will helps you.
Mar 15 2022 11:45 PM
Aug 22 2019 07:22 AM
SolutionHi
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:
=VLOOKUP($B5,CHOOSE(IF($A5<=$B$1,1,2),$G$5:$H$9,$G$14:$H$18),2,0)
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
Nabil Mourad