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)

Row	StaffNo	O-Bonus	O-Salary	O-Rating	Bonus	Salary	Rating	DateChanged	E-Bonus	E-Salary	E-Rating
1	123456	1000	2000			21000		1/12/2018	1000	21000
2	123456	1000	2000		1250			2/12/2018	1250	21000
3	123456	1000	2000		1500			3/12/2018	1500	21000
4	123456	1000	2000			25000		4/12/2018	1500	25000
5	123456	1000	2000				C3	5/12/2018	1500	25000	C3
6	123456	1000	2000		900			6/12/2018	900	25000	C3
7	123456	1000	2000				A2	7/12/2018	900	25000	A2
8	123456	1000	2000			15000		8/12/2018	900	15000	A2
9	123456	1000	2000		950	19500	B1	9/12/2018	950	19500	B1

<tbody>
</tbody>

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]

Row	StaffNo	O-Bonus	O-Salary	O-Rating	Bonus	Salary	Rating	DateChanged	E-Bonus	E-Salary	E-Rating
1	123456	1000	2000			21000		01/12/2018	1000	21000
2	123456	1000	2000		1250			02/12/2018	1250	21000
3	123456	1000	2000		1500			03/12/2018	1500	21000
4	123456	1000	2000			25000		04/12/2018	1500	25000
5	123456	1000	2000				C3	05/12/2018	1500	25000	C3
6	123456	1000	2000		900			06/12/2018	900	25000	C3
7	123456	1000	2000				A2	07/12/2018	900	25000	A2
8	123456	1000	2000			15000		08/12/2018	900	15000	A2
9	123456	1000	2000		950	19500	B1	09/12/2018	950	19500	B1
		in J2 (ebonus of 1000) you need this formula
		=IF(F2="",C2,F2)

<colgroup><col span="8"><col><col span="5"></colgroup><tbody>
</tbody>

 

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

Row\Column	A	B	C	D	E	F	G	H	I	J	K
1	StaffNo	O-Bonus	O-Salary	O-Rating	Bonus	Salary	Rating	DateChanged	E-Bonus	E-Salary	E-Rating
2	123456	1000	20000			21000		1/12/18	1000	21000
3	123456	1000	20000		1250			2/12/18	1250	21000
4	123456	1000	20000		1500			3/12/18	1500	21000
5	654321	1111	33333	A2	2222			3/12/18	2222	33333	A2
6	123456	1000	20000			25000		4/12/18	1500	25000
7	123456	1000	20000				C3	5/12/18	1500	25000	C3
8	123456	1000	20000		900			6/12/18	900	25000	C3
9	123456	1000	20000				A2	7/12/18	900	25000	A2
10	654321	1111	33333	A2			A1	7/12/18	2222	33333	A1
11	123456	1000	20000			15000		8/12/18	900	15000	A2

<tbody>
</tbody>

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.