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 )

Option Explicit
Dim CurrentCostColumn2 As String

Private Sub Worksheet_selectionChange(ByVal Target As Range)
If Target.Column = 2 Then Let CurrentCostColumn2 = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 2 And CurrentCostColumn2 <> "" Then Let Target.Offset(0, 3).Value = CurrentCostColumn2
End Sub

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

[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

Option Explicit
Dim CurrentCostColumn2 As String
Private Sub Worksheet_selectionChange(ByVal Target As Range)
    If Target.Column = 2 Then Let CurrentCostColumn2 = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Rem 0) User defined Error handler
 On Error GoTo TheEnd ' Always a good idea, especially in codes that may turn things off (like the Application.EnableEvents = False), too ooveride the default Error handling with this which will not prevent an error situation occuring, but on such an event, the code will continue at the spring point sent to. There you cann do anything that shoould always be done , even if the code errors
Rem 1) Alan's Bit
    If Target.Column = 2 And CurrentCostColumn2 <> "" Then ' I could ignoor the <> "" check and just paste the "" in for the first entry, as for such a case the previous entry was ""
     Application.EnableEvents = False ' This stops the code kicking in again at the next line. One problem if that did occur is that the Target Range would then change.
     Target.Offset(0, 3).Value = CurrentCostColumn2
     Application.EnableEvents = True ' It is always a good idea to switch anything back on as sooon as possible
    Else ' case no change in column 2
    End If
Rem 2) Put current date of entry in column F
' 2a) This repeats some of my code , making a Range object of the cross over of Target and column B, then checking to see if that exists as an indication of a valid range ( coolumn B ) hit
Dim WorkRng As Range                                                          ' Dim: For the Variable type declared, there should be some Blue Print or master Instruction list. If not The code will error at the start as it need to examine and therefore prepare for usage of the variable in the code run. The variable itself will go to some “Pigeon hole” that has a copy of the instructions or in some way has information on how to use the original instructions such that it can distinguish between different variables of the same type. When the code meets the variable it will look in the Pigeon Hole Pointer for instructions as to what to do in various situations. Knowing of the type allows in addition to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ). Note this is a look up type list and may not be a guarantee that every offered thing is available – most will be typically.
Dim Rng As Range
Dim xOffsetColumn As Long ' Integer'_- If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )      Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647)
 Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)        ' Set: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. Wed will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
 '  xOffsetColumn = 1 ?
    If Not WorkRng Is Nothing Then '                                                                      checking to see if that exists as an indication of a valid range ( coolumn B ) hit
     Let Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then ' only if target cell is not empty
             Let Rng.Offset(0, 4).Value = Now                            '  Curren date in..
             Let Rng.Offset(0, 4).NumberFormat = "dd-mm-yyyy, hh:mm:ss"  '  .. a specific format
            Else
             Rng.Offset(0, 4).ClearContents ' column F cell if column B cell was empty
            End If
        Next
     Let Application.EnableEvents = True
    End If
TheEnd: ' Error handling code section. Put anything here that should be done in any case before the code ends
 Let Application.EnableEvents = True
End Sub

[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

Option Explicit
Dim SelVls() As Variant ' Array to hold value of a selection
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' To capture current selecton values
    If Target.Column = 2 And Target.Count = 1 Then ' Case single cell selection
     ReDim SelVls(1 To 1, 1 To 1): Let SelVls(1, 1) = Target.Value
    ElseIf Target.Column = 2 And Target.Columns.Count = 1 Then ' case a multi cel selection in column B
     Let SelVls() = Target.Value ' for more than one cell, the .Value Property returns a field of Variant types. These may be aplied directly to a dynamic array delcared (Dim ed) as Variant types, and the Array will be sized appropritely to suit
    Else ' Possibility here to do something else for a another type of selection
    End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range) ' On Entries the last entries should be copied to column E, and the entry date to column F
'User defined Error handler
 On Error GoTo TheEnd ' Always a good idea, especially in codes that may turn things off (like the Application.EnableEvents = False), too ooveride the default Error handling with this which will not prevent an error situation occuring, but on such an event, the code will continue at the spring point sent to. There you cann do anything that shoould always be done , even if the code errors
' The working Range is the Target Range for Target Column of 2 And count of target coumns of  1
'Dim WorkRng As Range                                                                 '  Dim: For the Variable type declared, there should be some Blue Print or master Instruction list. If not The code will error at the start as it need to examine and therefore prepare for usage of the variable in the code run. The variable itself will go to some “Pigeon hole” that has a copy of the instructions or in some way has information on how to use the original instructions such that it can distinguish between different variables of the same type. When the code meets the variable it will look in the Pigeon Hole Pointer for instructions as to what to do in various situations. Knowing of the type allows in addition to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ). Note this is a look up type list and may not be a guarantee that every offered thing is available – most will be typically.
' Set WorkRng = Target                                                                '  Set: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. Wed will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
    If Target.Column = 2 And Target.Columns.Count = 1 Then ' case a multi cel selection in column B
' Alan's Bit put previous or old price in column E
     Let Application.EnableEvents = False ' This stops the code kicking in again at the next line. One problem if that did occur is that the Target Range would then change.
     Let Target.Offset(0, 3).Value = SelVls() ' We may assign the values of a range to the values of an Array
     Let Application.EnableEvents = True ' It is always a good idea to switch anything back on as sooon as possible
' Original Code bit done in Arrays to put date/times in an Array for output
    Dim arrDts() As Variant ' (Date would cause 00.01.1900  00:00:00 for Empty)        '  http://www.snb-vba.eu/VBA_Arrays_en.html#L_5.3.1.4   5.3.1.4 Declare a multi-dimensional dynamically sized array-variable
     ReDim arrDts(1 To UBound(SelVls(), 1), 1 To 1) '                                  '  Dim determines the size of an array before it is being used or filled.  The size will be fixed as long as the variable exists.It's not always possible to know the desired array size beforehand.In that case you can use the method Redim. Unlike the method Dim the method ReDim accepts variables, expressions, etc. to indicate the array's size.
    Dim cnt
        For cnt = 1 To UBound(arrDts(), 1) Step 1
         If Target(cnt, 1) <> Empty Then Let arrDts(cnt, 1) = Now ' The corresponding position in the array for dates/time  update is updated with current date/time unless the inputed value was empty, in which case the Array will be left at "", which by final pasting in will effectively clear any date there
        Next cnt
' Output date/time in or clear column F
     Let Application.EnableEvents = False
     Let Target.Offset(0, 4).Value = arrDts()
' Format particullar date format in column F
     Let Target.Offset(0, 4).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
     Let Application.EnableEvents = True
    Else ' case false selection
    End If
TheEnd:     ' Error handling code section. Put anything here that should be done in any case before the code ends
     Let Application.EnableEvents = True ' (Does no harm too try to turn it on if it already is)
End Sub

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