SOLVED

change price according to date

Copper Contributor

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?

 

7 Replies

@RRRau 

 

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.

PasteSpecialAsValuesAndNumberFormatting.jpg

 

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.

best response confirmed by RRRau (Copper Contributor)
Solution

@RRRau 

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

two Tables Lookup.png

 

 

Hope that Helps

Nabil Mourad

@nabilmourad  perfect! thank you so much

@RRRau 

Wonderful

I'm glad I was able to help you

Good Luck

Nabil Mourad

Hi @nabilmourad , thanks for this info.
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.

@vacayshane 

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:

 

Abdul_Ahad_Khatri_0-1646887571603.png

 

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
1 best response

Accepted Solutions
best response confirmed by RRRau (Copper Contributor)
Solution

@RRRau 

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

two Tables Lookup.png

 

 

Hope that Helps

Nabil Mourad

View solution in original post