Latest

[KojakWeb]

Hi,


I'm using Excel 2013 and maintaining a table which logs changes to one or more fields. The table below illustrates an example of bonus/salary/rating that could be adjusted during the project. The "O-" prefixed fields are a lookup to the original load-up data in another table. The values that I would expect to see generated by excel are in the "E-" prefixed fields. (NOTE: at this particular client I cannot use macros/plug-ins and would prefer avoiding vba)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]StaffNo[/TD]
[TD]O-Bonus[/TD]
[TD]O-Salary[/TD]
[TD]O-Rating[/TD]
[TD]Bonus[/TD]
[TD]Salary[/TD]
[TD]Rating[/TD]
[TD]DateChanged[/TD]
[TD]E-Bonus[/TD]
[TD]E-Salary[/TD]
[TD]E-Rating[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD]21000[/TD]
[TD][/TD]
[TD]1/12/2018[/TD]
[TD]1000[/TD]
[TD]21000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]1250[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/12/2018[/TD]
[TD]1250[/TD]
[TD]21000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD]3/12/2018[/TD]
[TD]1500[/TD]
[TD]21000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]4/12/2018[/TD]
[TD]1500[/TD]
[TD]25000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C3[/TD]
[TD]5/12/2018[/TD]
[TD]1500[/TD]
[TD]25000[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]900[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/12/2018[/TD]
[TD]900[/TD]
[TD]25000[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[TD]7/12/2018[/TD]
[TD]900[/TD]
[TD]25000[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD]15000[/TD]
[TD][/TD]
[TD]8/12/2018[/TD]
[TD]900[/TD]
[TD]15000[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]950[/TD]
[TD]19500[/TD]
[TD]B1[/TD]
[TD]9/12/2018[/TD]
[TD]950[/TD]
[TD]19500[/TD]
[TD]B1[/TD]
[/TR]
</tbody>[/TABLE]



As you can see, on different days I'll be instructed to change values for different fields. I'm struggling of thinking of a formula that would give the value I want to see in the "E-" fields generated in the above example, i.e. the latest adjusted figure that exists, otherwise it should use the value from the original load-up table. As you can see the values in the example above can go adjusted down as well as up. In essence I'm trying to avoid having to key in the values in all those three fields every time a new row is created. Thanks in advance.

 

[oldbrewer]

[TABLE="width: 930"]
<colgroup><col span="8"><col><col span="5"></colgroup><tbody>[TR]
[TD]Row[/TD]
[TD]StaffNo[/TD]
[TD]O-Bonus[/TD]
[TD]O-Salary[/TD]
[TD]O-Rating[/TD]
[TD]Bonus[/TD]
[TD]Salary[/TD]
[TD]Rating[/TD]
[TD]DateChanged[/TD]
[TD]E-Bonus[/TD]
[TD]E-Salary[/TD]
[TD]E-Rating[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21000[/TD]
[TD][/TD]
[TD="align: right"]01/12/2018[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]21000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD="align: right"]1250[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/12/2018[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]21000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/12/2018[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]21000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25000[/TD]
[TD][/TD]
[TD="align: right"]04/12/2018[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C3[/TD]
[TD="align: right"]05/12/2018[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]25000[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD="align: right"]900[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/12/2018[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]25000[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[TD="align: right"]07/12/2018[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]25000[/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15000[/TD]
[TD][/TD]
[TD="align: right"]08/12/2018[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]15000[/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]19500[/TD]
[TD]B1[/TD]
[TD="align: right"]09/12/2018[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]19500[/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]in J2 (ebonus of 1000) you need this formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]=IF(F2="",C2,F2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[KojakWeb]

Hi. I'm afraid that formula won't work for my purposes (perhaps I should have excluded row 9 to make it clearer). E.g. looking at just bonus, that formula would work for row 3 where the bonus was adjusted to be 1500, but as there is no value in the following few rows, it would return what is column 1000. I need the bonus of 1500 to be carried forward to subsequent rows until row 6, where we can see that the bonus has been entered as 900.

 

[Fluff]

In j2 copied over
=IF(F2="",C2,F2)
in J3 copied over & down
=IF(F3="",J2,F3)

 

[KojakWeb]

That assumes that no other rows appear with other staff numbers, and assumes that the table won't be resorted (which is why I have staffno and date as entered values). Let me re-frame my example, by introducing another employee.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row\Column[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]StaffNo[/TD]
[TD]O-Bonus[/TD]
[TD]O-Salary[/TD]
[TD]O-Rating[/TD]
[TD]Bonus[/TD]
[TD]Salary[/TD]
[TD]Rating[/TD]
[TD]DateChanged[/TD]
[TD]E-Bonus[/TD]
[TD]E-Salary[/TD]
[TD]E-Rating[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD]21000[/TD]
[TD][/TD]
[TD]1/12/18[/TD]
[TD]1000[/TD]
[TD]21000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD]1250[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/12/18[/TD]
[TD]1250[/TD]
[TD]21000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD]3/12/18[/TD]
[TD]1500[/TD]
[TD]21000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]654321[/TD]
[TD]1111[/TD]
[TD]33333[/TD]
[TD]A2[/TD]
[TD]2222[/TD]
[TD][/TD]
[TD][/TD]
[TD]3/12/18[/TD]
[TD]2222[/TD]
[TD]33333[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]4/12/18[/TD]
[TD]1500[/TD]
[TD]25000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C3[/TD]
[TD]5/12/18[/TD]
[TD]1500[/TD]
[TD]25000[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD]900[/TD]
[TD][/TD]
[TD][/TD]
[TD]6/12/18[/TD]
[TD]900[/TD]
[TD]25000[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[TD]7/12/18[/TD]
[TD]900[/TD]
[TD]25000[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]654321[/TD]
[TD]1111[/TD]
[TD]33333[/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD]A1[/TD]
[TD]7/12/18[/TD]
[TD]2222[/TD]
[TD]33333[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]123456[/TD]
[TD]1000[/TD]
[TD]20000[/TD]
[TD][/TD]
[TD][/TD]
[TD]15000[/TD]
[TD][/TD]
[TD]8/12/18[/TD]
[TD]900[/TD]
[TD]15000[/TD]
[TD]A2[/TD]
[/TR]
</tbody>[/TABLE]

I hope that illustrates what I tried to describe in my original post.

 

[KojakWeb]

Don't know if anyone is able to help me on this, but I was thinking that I could use offset, with a way to dynamically change how it picks up the entry the previous time a field value was entered. In the example above, for rows 7-9, the salary amount would pick up the value in F6 which is 25k.

 

[KojakWeb]

I think I found a way to do it, it requires the addition unique identifier column for each of the fields that's recording a change. So for the Bonus field, it's creating a unique identifier, combining staffno and the last date a row received a bonus entry:

UniqueDateBonus
{=[@StaffNo]&IF([@Bonus]<>"",[@DateChanged],MAX(IF([StaffNo]=[@StaffNo],(IF([DateChanged]<[@DateChanged],IF([Bonus]<>"",[DateChanged]))))))}


Then it's an index/match to return the relevant bonus entry, unless the date component in the above formula returns a 0 (i.e. first row entered into the log doesn't contain an entered value for Bonus, or if all the entries in the log don't have a value for Bonus) in which case use the O-Bonus value.

E-Bonus
{=IF(IF([@Bonus]<>"",[@DateChanged],MAX(IF([StaffNo]=[@StaffNo],(IF([DateChanged]<[@DateChanged],IF([Bonus]<>"",[DateChanged]))))))=0,[@[O-Bonus]],INDEX([Bonus],MATCH([@UniqueDateBonus],[UniqueDateBonus],0)))}

I would welcome any suggestions if anyone has a more elegant way of doing this.