multiple cells updating from one cell

[A1058]

hello, hoping someone can solve my problem here.
i have 3 cells per page that are retrieving a max time value from another cell basedon a 3 other trigger cells in each sheet.. the triggers work and the cells get their value from the maxtime value cell. but when the 2nd or 3rd trigger cell values are true ALL the cells are reupdating their times.
basically it says IF L4 <> "" then BJ4 = BM46
and the same for
IF L16 <> "" then BJ16 = BM46
and again
if L28 <> "" then BJ28 = BM46
the target cells are grabbing the Bm46 values but as bm46 is updated over and over again the target cells in column BJ are also constantly updated. How can i make the cells keep their Initial values without locking the sheet as there are many many entries on each sheet a user constantly need access to use.
any help would be greatly appreciated

 

[pgc01]

Hi

I cannot understand your problem.

Please tell what happens as BM46 changes value, what you'd like to happen, and why (the conditions that determine the values of the other cells).

Please illustrate it with a working example with BM46 changing 4 or 5 times (or less if you think it's clear), like

BM46 is updated to Val1 -> what happens, what you'd like to happen, why
BM46               Val2 -> 
BM46               Val3 -> 
BM46               Val4 ->

Kind regards
PGC

 

[A1058]

BM46 is the maxvalue of 45 possible stop times there are 3 processes per page which im trying to record start and stop times for, and all stop times are recorded into BM1:BM45 BM46 is simply the MaxValue of this column. what i am looking for is when L4, L16 or L28 receive a value from the user (this is signifying that the next items process has begun...IE start time..)then the corresponding start time boxes for would grab a value from the BM46 cell since the process is nonstop the last stop time should equal the next start time, cant simple say start time = previous items stop time though because they may be run out of order. but thats no longer the problem. i can make the start time boxes grab the correct time value but they overwrite each other, say.... item 1 is finished it has a start and stop time and calculated total runtime as well. problem is when the next trigger value is TRUE or L16<> "" then L16 initiates BJ16 to grab a start time from BM46 but in doing so will overwrite BJ4's start time also
what im trying to do is avoid this. so once BJ4 has a value it cannot be reupdated and the same for BJ16 and BJ28.... god i hope i didnt confuse you i think i may have confused myself now.. the workbook is a bit complex. if you like i would be happy to email you a copy to look over

 

[pgc01]

Hi again

I'm not sure I understood you well. Here is what I understood

You have three pairs Trigger cell - Time cell

L4 - BJ4
L16 - BJ16
L28 - BJ28

The trigger cells will change over time between the values < empty > and TRUE several times.

Each time one of the Trigger cells changes from < empty > to TRUE you want to assign the corresponding Time cell the value in the cell BM46

Example

L4 changes from < empty > to TRUE, you assign BJ4=BM46 (BJ16,BJ28 don't change)
L28 changes from < empty > to TRUE, you assign BJ28=BM46 (BJ4,BJ16 don't change)
L4 changes from TRUE to < empty >. Nothing happens (? or you prefer to empty BJ4?)
L4 changes from < empty > to TRUE again, you assign BJ4=BM46 (BJ16,BJ28 don't change)

Did I understood right?

If this is the case, if I'm not wrong, there is no solution with formulas. You have to use the worksheet Change Event.

Hope this helps
PGC

 

[A1058]

you are understanding correctly now... you got it.. except if L4 L16 or L28 ever went from TRUE to empty which they never should do.. i would want nothing to happen in the BJ4 BJ16 B28<---- basically once they have a value they never are to change. i was pretty sure there was nothing to do with formulas. but there has to be a way to make it work... someones gotta know how!!!

 

[pgc01]

Hi again

I still don't understand completely. The values of L4, L16 and L28 only change once, from empty to non empty?

Anyway this is a Change Event macro that will do the following:

If L4, L16 or L28 change value and their final value is non empty then the corresponding cell BJ4, BJ16 or B28 (only one) will receive the value of BM46.

Since this is an Event macro it must be placed in the corresponding worksheet module. For ex. if these cells are in sheet1 right click the tab, choose view code. The vba editor opens and you see that the sheet1 module is selected.

Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$L$4" Or Target.Address = "$L$16" Or Target.Address = "$L$28" Then
   If Target.Value <> "" Then
       Range("BJ" & Target.Row).Value = Range("BM46").Value
   End If
End If
End Sub

Please test it for example like this.

Clear BJ4, BJ16, BJ28
Enter a value in BM46.
Enter any value in L4. Check that BJ4 is equal to BM46 and BJ16 and BJ28 are still empty.
Continue changing the value in BM46 and in L4, L16 and L28 and check how only the corresponding cell changes value.

Hope this helps
PGC

 

[A1058]

testing this now... seems to work !!! im off to work though. i will reply tonight after ive had time to go through this, thank you very much for your help, you made this seem quite simple .