Forum Discussion
change price according to date
Hi
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
Hi
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
1.)Is it possible to change the reference for cut off date per row? Say for D5, the cut off date is A5.
So any price before A5 date will not be captured
2.) Say prices change almost weekly, can we have 1 table for price list but with multiple columns?
3.) How about if we have 2 price kind per cut-off date? How should we put it.
Thank you in advance for your help.
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.
- thanks Abdul_Ahad_Khatri , i will still have to try this. Will this perhaps work if i put it in a file like in this video? https://www.youtube.com/watch?v=7J26VB3mldM Google Sheets Purchase Order Generator: Using Purchase Order Template and Google Apps Script
