Auto update List

[isameer247]

Hi everyone,

I have a sheet in which some data automatically updated. Due to this in one cell the data is auto changed.

for example


in cell d5 some times ''on'' displays some time ''off'' displays some time ''neutral' displays. I want a formula which list all tha changes in a column.

means when d5= on then e1 should be on when d5= off then e2 should be off when d5= neutral then e3 should be neutral when d5= on then e4 should be on.

Kindly help me on this i'll be very thankful to you.


Regards,
Sameer

[NeedForExcel]

Here!

Put this on E1 & E4 - =IF(D5="On","On","")
On E2 - =IF(D5="Off","Off","")
On E3 - =IF(D5="Neutral","Neutral","")

Hope this is what you want.. If not, please clarify!

[isameer247]

sorry you are not getting my point. If i put your formula then when d5 changes all E column will be change. I want a formula which shows every new indicator from d5 in column E. if the data changes 1000 times then E columns 1000 rows should be filled.

[NeedForExcel]

Sorry!

Put this in your regular code module -

Sub UpdateChanges()
    
    Dim LRColE As Integer, ValStart As String
    
    Application.EnableEvents = False
    
    ValStart = Range("D5").Value
    
    LRColE = Cells(Rows.Count, "E").End(xlUp).Row
    
    If Cells(LRColE, "E").Value <> "" Then LRColE = LRColE + 1
    
    Cells(LRColE, "E").Value = ValStart
    
    Application.EnableEvents = True

End Sub

And put this on your worksheet module -

Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateChanges
End Sub

[isameer247]

thank you for your code. As I am totally new to VBA can you please tell me where to put those formulas.....

[NeedForExcel]

First of all you will have to Enable The Developer Tab -

http://www.needforexcel.com/#!Enable...7-85EDEE74C7C5

Once the Developer Tab is enabled, then use Alt+F11 > Inset Tab > Module

Put the Larger Code here

Then On the Worksheet you want the code to run > Right Click On the Sheet Tab Name > View Code

Put the smaller code here.

Do not forget to keep macros enabled.

I am unable to attach sheet for some reason, else would have helped you with it..