I need a formula for a previous or old price

[X myth]

Hi. Im pretty new to Excel. Could anyone help me with a solution? I need a formula for a previous or old price. When I put in a new price i'd like to see the old one.

----------A-----------B------------------C----------D----------E---------
1: [ItemsName][Cost Price][Previous Cost][Deal][Mark Up%]
2:
3:

So I'd like to change my cost price, but automatically transfer the old price into the Previous Cost Cell

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[X myth]

Hi, welcome to the forum

Thank You! Added the file as suggested.

[FDibbins]

Thanks for the file, can you walk me through what you are doing here?

[X myth]

Cost is the current cost in B5 so when there is a mark up I will put the new cost in there.

Example: If I change the cost from 55 to 65 I would like the previous cost of 55 to be recorded and put into E5 "Prev. Cost"

[FDibbins]

OK so you want to enter a value in B5, then, when you enter a new value in B5, you want the old value to put copied/put into another cell?

Do I understand that correctly?
Because if that is what you want, you cant do that with formulas, you would need VBA/macro for that

[X myth]

Yes correct. VBA/Macros never tried that =D Is it something you can help me with?

[FDibbins]

VBA is my weakness

Im sure that other members will assist with this for you

[X myth]

VBA is my weakness

Im sure that other members will assist with this for you

Thanks though, atleast I can stop looking for an impossible function =D Ill check back tomorrow. Hopefully there will be some options I can try out! Thanks again

[avk]

Dear Xmyth, Refer attach file.
In this file i made some modification changes in sheet2 add column "Helper"
Sheet1 No any changes.
In sheet1 suppose "B5" 55 is your old cost. Kindly change as 65.
In "E5" privious prices shown even you have enter new cost enter in "B5"
Using index match formula.

[Doc.AElstein]

Hi X myth
Welcome to the Forum.
Your requirement is a good example of something that is very difficult ( if not impossible ) , with formulas , but is incredibly easy in VBA.
Try the File enclosed and see if it is something like you want.
( When you "open" it you will need to trust ( me ) that the macro I wrote is safe, and enable macros when it ( if ) it asks you if you want to )

If you Know nothing about macros then it is probably not a good idea for me to explain too much of what is going on, (yet). But I will be very happy to explain more later if you want..

Very briefly, this what is going on in two codes I wrote in the File I did for you:

Sub Worksheet_selectionChange(ByVal Target As Range)
' Every time you click on a cell in column “B”, ( Column 2 ) , the value ( .Value ) in the cell is recorded.

Sub Worksheet_Change(ByVal Target As Range)
' Every time the value is changed, the last value , ( if that last value wasn’t no value ) is put in the Column which is offset 3 columns to the right, ( 3 steps to the right from column B is Column E ).

So when you click in a cell in Column B, the cell value is recorded ( even if it is empty ). The first code does that.
The second code "kicks" in at the change in a cell value in Column B. ( That happens when you hit Enter, after you have changed a cell value )

( The code really should be written a lot better to cope with all different situations that might cause problems, but I keep it simple for now to see if it is anywhere near what you want ).
Alan

_...
Code ( It is in the File )

Please Login or Register  to view this content.

[FDibbins]

Thanks for the assist Doc

[Doc.AElstein]

Hi Ford, Hi avk..

Thanks for the assist Doc

But..... avk did it with a formula
I am busy trying to understand that now.
It is great when you get different solutions like this , thanks avk
Alan

EDIT : @ avk
Does your formula actually work???
I just tried and it does not seem to ???

[X myth]

Hi Ford, Hi avk..

But..... avk did it with a formula
I am busy trying to understand that now.
It is great when you get different solutions like this , thanks avk
Alan

EDIT : @ avk
Does your formula actually work???
I just tried and it does not seem to ???

Hi Doc. This is exactly what I was looking for.
Thank you very much! I will try to dissect what you have done so I can understand it.

[Doc.AElstein]

Hi X myth
WhoWhat.JPG


To who, and to what ( who's) solution are you talking about ??

Alan

[X myth]

Oops sorry I quoted the wrong message.
Yours worked for me DOC Thank you!

Avk's did not work for me.

[Doc.AElstein]

...
Avk's did not work for me.

It does not work for me either ... I get the feeling sometimes that Ford ( FDibbins ) knows about these formula things , ( I don't - I find VBA a lot easier - and aI am not a programmer by trade or education ) , - so I expect if he says it is not possible with a formula then probably it isn't
Alan

[avk]

Oops sorry I quoted the wrong message.
Yours worked for me DOC Thank you!

Avk's did not work for me.

Can you explain what is not work actually. You need if changed old price to new price old price visible.

[X myth]

Can you explain what is not work actually. You need if changed old price to new price old price visible.

Avk, Thanks for helping. The problem is when you change the number in B the number in E does not change to the previous number that B use to be.
Download Doc's file that uses VBA how he has it set is exactly what I was looking for. Thanks again though.

[X myth]

It does not work for me either ... I get the feeling sometimes that Ford ( FDibbins ) knows about these formula things , ( I don't - I find VBA a lot easier - and aI am not a programmer by trade or education ) , - so I expect if he says it is not possible with a formula then probably it isn't
Alan

Doc or anyone that knows how VBA works, how would I add this to the code you gave me?

This:
---------------------------------------------------------------------------------------

Please Login or Register  to view this content.

---------------------------------------------------------------------------------------
I keep getting an error when I just paste it below your code.

Thank you again, and here is an updated version of the file I'm working on with your code added.

[Doc.AElstein]

Can you explain what is not work actually. You need if changed old price to new price old price visible.

@avk
Hi avk.
Just to clarify:
This is how your File downloads:
Using Excel 2007 32 bit

Row\Col	B	C	D	E
4	This is Old Price. Kindly change as 65 & see in "E"
5	55	5	77.00	55

Worksheet: Sheet1

Now I type 65 in B5 and I see this

Row\Col	B	C	D	E
5	65	5	91.00	55

Now I type 75 in B5 and I see this

Row\Col	B	C	D	E
5	75	5	91.00	55

The last screenshot is incorrect.
X myth would now like to see the following:

Row\Col	B	C	D	E
5	75	5	105.00	65

Always in E5 should be the previous value from B5
Alan  

[Doc.AElstein]

Hi X myth
_1) Please use Code Tags when posting code. Please when you have the time read through the Forum Rules and posting Suggestions:
http://www.excelforum.com/forum-rule...rum-rules.html
http://www.excelforum.com/developmen...ml#post4606484

_.______________________________________

_2)

...I keep getting an error when I just paste it below your code....

You must give a lot more detail if you need help. You’ve given a code without any information about what it is it is supposed to do. What do you want the code to do? What is your requirement. What are you wanting to do???

Alan

[FDibbins]

Thanks for the assist, Alan

[Doc.AElstein]

I modified the codes to "work", but we nneed to think again I think....

I will take a guess that what you want is for when entries are made in column B , you would then, at that time, want the date and time of those entries to be written in column F. So column F shows the date of the last entries made in column B?

The code you gave is written to do that for any number of entries at one time in column B. So like if you paste in a whole lot of entries in one go , then all the corresponding rows in column F will be updated.

I think we really should have started with that code, and then the question would have been for that to be modified to put the previous entries in column E. But that assumed you/ we knew that a formuula was not possible, which you / we didn't. So, no worries....
But, I just mentioned that as the code below is an inefficient mess now, and will only work for one entry at a time, as that is what my first simple code did. My code snippet would error, and so will this new code for more than one entry at a time.

But I will post it anyway just for fun , and then start again and write a code from scratch to do what I expect you actually are needing to do



Alan

Please Login or Register  to view this content.

[avk]

@avk
Hi avk.
Just to clarify:
This is how your File downloads:
Using Excel 2007 32 bit

Row\Col	B	C	D	E
4	This is Old Price. Kindly change as 65 & see in "E"
5	55	5	77.00	55

Worksheet: Sheet1

Now I type 65 in B5 and I see this

Row\Col	B	C	D	E
5	65	5	91.00	55

Now I type 75 in B5 and I see this

Row\Col	B	C	D	E
5	75	5	91.00	55

The last screenshot is incorrect.
X myth would now like to see the following:

Row\Col	B	C	D	E
5	75	5	105.00	65

Always in E5 should be the previous value from B5
Alan  

I have tested in sheet1
The result show as per below.

[JohnTopley]

@AVK :

When you type in 75 , result in should be 65 (last price prior to latest change) ?????

[Doc.AElstein]

Hi John,

...When you type in 75 , result in should be 65 (last price prior to latest change) ?????

Correct

[Doc.AElstein]

Hello
Here you go:

You start Before the code runs with this: X mythBefore.JPG http://imgur.com/SupQNBeX mythBefore.JPG


Now say I paste this_..

45

74

_.. across B3:B5

This then becomes the After: X mythAfter.JPG http://imgur.com/x9Dt54SX mythAfter.JPG


The code probably still is not perfect and might not work in all situations or do exactly what is needed , whatever they might be or is.

Alan


Code

Please Login or Register  to view this content.

[X myth]

Hello
Here you go:

Thanks Doc, you've been incredibly helpful!
I owe you a Beer! and not just one off this sheet you've been helping me with

I really appreciate the tutorials/lessons.

[Doc.AElstein]

Hi

..owe you a Beer! and not just one off this sheet .....

I have probably been too long in the Fatherland, the only thing I can recognise there is a Worksheets(“Horizon”).Range(“A35”).CanOfSix....
Thanks for the Rep thingies, (shame there not fluid Heineken thingies :-) )
Cheers !
Alan

P.s. , when you get a chance...
_1) Have a go at marking the Thread as Solved , ( if you are finished with it ) https://www.excelforum.com/developme...ml#post4605543
_ 2) Try to go through the Rules before you post again, - we don’t have many, and most of them are reasonable.. like.. you shouldn’t Duplicate posts .. this here was borderline on that ... https://www.excelforum.com/excel-pro...ml#post4617523 . Certainly if you do follow on with a similar or strongly related question to an existing Thread, then make a point of mentioning and cross referencing all Threads with the link to the Thread copied from your Browser URL Bar , - That way we can all see what is going on and not Duplicate / waste our efforts

[avk]

Dear JohnTopley (For #26) Yes i got the point. In this case solution only vba macro.