SOLVED
Home

change price according to date

%3CLINGO-SUB%20id%3D%22lingo-sub-816537%22%20slang%3D%22en-US%22%3Echange%20price%20according%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816537%22%20slang%3D%22en-US%22%3E%3CP%3EI%20manage%20a%20spreadsheet%20of%20sales%20data.%3C%2FP%3E%3CP%3Ewe%20only%20have%205%20products.%20but%20they%20keep%20changing%20the%20price.%3C%2FP%3E%3CP%3EI%20currently%20use%20a%20vlookup%20to%20get%20the%20correct%20price%20-%20that%20works%20fine.%20BUT%20if%20i%20change%20the%20price%20in%20the%20Vlookup%20all%20the%20old%20sales%20also%20change%20and%20i%20dont%20want%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Sales%20after%20Sept%201%20%3D%20%24x%20but%20sales%20before%20Sept%201%20%3Dy%3C%2FP%3E%3CP%3Ewhat%20is%20the%20best%20way%20to%20handle%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-816537%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816682%22%20slang%3D%22en-US%22%3ERe%3A%20change%20price%20according%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816682%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396456%22%20target%3D%22_blank%22%3E%40RRRau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20easiest%20workaround%20is%20after%20placing%20the%20formulas%2C%20copy%20the%20formula%20cells%20(which%20actullay%20returns%20prices%20and%20you%20don't%20want%20them%20to%20change%20next%20time%20when%20you%20change%20the%20price)%20and%20paste%20them%20back%20as%20%22Values%20and%20number%20format%22.%3C%2FP%3E%3CP%3EThis%20is%20how%20you%20should%20paste%20the%20copied%20cells%20back%20as%20Values%20and%20number%20formatting.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20741px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127983iB4927D8089B9D017%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22PasteSpecialAsValuesAndNumberFormatting.jpg%22%20title%3D%22PasteSpecialAsValuesAndNumberFormatting.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20after%20copying%20the%20formula%20cells%2C%20you%20may%20use%20the%20shortcut%20key%20%3CSTRONG%3EAlt%2C%20E%2C%20S%2C%20U%2C%20Enter%3C%2FSTRONG%3Eto%20paste%20them%20back%20as%20Values%20and%20number%20formatting.%3C%2FP%3E%3CP%3ERemember%20you%20can%20only%20use%20this%20shortcut%20key%20when%20the%20clipboard%20is%20not%20empty%20i.e.%20you%20have%20copied%20something.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOtherwise%20you%20may%20consider%20a%20Macro%20to%20insert%20the%20formula%20in%20selected%20cells%20and%20then%20convert%20them%20back%20as%20values%20so%20in%20the%20end%20the%20worksheet%20will%20not%20have%20any%20formulas%20and%20next%20time%20when%20you%20need%20to%20lookup%20the%20price%20for%20different%20set%20of%20cells%2C%20use%20the%20macro%20again%20to%20insert%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-817268%22%20slang%3D%22en-US%22%3ERe%3A%20change%20price%20according%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-817268%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396456%22%20target%3D%22_blank%22%3E%40RRRau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThis%20is%20a%20common%20situation.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20have%20multiple%20prices%20based%20upon%20different%20criteria%20(Say%20Store%20Regions%2C%20Clients%20Categories...etc)%3C%2FP%3E%3CP%3EIn%20your%20question%20it%20is%20a%20DATE%20Cutoff%20(Before%20or%20After%20a%20specific%20date%20Say%201%20September%202019%3C%2FP%3E%3CP%3EI%20created%20a%20sample%20File%20for%20you%20with%202%20Price%20lists%20and%20in%20Cell%20B1%20you%20enter%20the%20CutOff%20date%3C%2FP%3E%3CP%3EIn%20the%20setup%3C%2FP%3E%3CP%3Ewe'll%20extract%20the%20price%20with%20a%20VLOOKUP%20function.%20However%20the%20Table%20array%20can%20be%20Price%20List%201%20or%202%20based%20upon%20the%20DateSo%20There%20is%20an%20IF%20function%20that%20selects%20the%20Table%20number%20(1%20or%202%20)%20based%20upon%20the%20cutoff%20Date.%3C%2FP%3E%3CP%3EThis%20if%20Function%2C%20is%20the%20first%20argument%20of%20a%20CHOOSE%20function%3A%20in%20case%20the%20IF%20returns%20%221%22%20then%20the%20CHOOSE%20selects%20the%20first%20Price%20List%20and%20provides%20it%20as%20a%20Table%20Array%20to%20the%20VLOOKUP%20function.%20Same%20concept%20for%20dates%20after%20the%20CutOff%20date.%3C%2FP%3E%3CP%3Ehere%20is%20also%20a%20screenshot%20and%20the%20Function%20is%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%233366FF%22%3E%3DVLOOKUP(%24B5%2C%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF0000%22%3ECHOOSE(%3C%2FFONT%3E%3CFONT%20color%3D%22%23339966%22%3EIF(%24A5%26lt%3B%3D%24B%241%2C1%2C2)%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF0000%22%3E%2C%24G%245%3A%24H%249%2C%24G%2414%3A%24H%2418)%3C%2FFONT%3E%3CFONT%20color%3D%22%233366FF%22%3E%2C2%2C0)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20tested%20it%20and%20it%20is%20working%20fine%3C%2FP%3E%3CP%3EOf%20course%20the%20setup%20can%20be%20modified%20as%20needed%20with%20more%20Price%20Lists%20and%20more%20conditions%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128021i9207BCCABDBED1C0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22two%20Tables%20Lookup.png%22%20title%3D%22two%20Tables%20Lookup.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818685%22%20slang%3D%22en-US%22%3ERe%3A%20change%20price%20according%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%20perfect!%20thank%20you%20so%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818697%22%20slang%3D%22en-US%22%3ERe%3A%20change%20price%20according%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396456%22%20target%3D%22_blank%22%3E%40RRRau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWonderful%3C%2FP%3E%3CP%3EI'm%20glad%20I%20was%20able%20to%20help%20you%3C%2FP%3E%3CP%3EGood%20Luck%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
RRRau
New 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?

 

4 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.

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

Related Conversations