Update cell value based on latest changed cell

Question

I am currently working on excel worksheet where I have to capture the value of the latest changed cell into another cell, without using VBA

scenario:

Cell A2 - should contain the latest cell value Cell B2 - where the value inputed Cell D2 - where the value inputed Cell F2 - where the value inputed Cell H2 - where the value inputed

so in cell A2, it should capture the value of the latest changed cell B2, D2, F2, H2, without using VBA

here is the sample output:

sample ouput

Answer 1

This will work to retrieve the furthest right entry for each row.

The formula will need to be entered as an array (when in the formula bar hit Ctrl+Shift+Enter)

=INDEX(A2:H2,1,MAX(IF(B2:H2<>"",COLUMN(B2:H2))))

Array formula's break down ranges and calculate them one cell at a time. The range that MAX is searching gets broken down as an array, the IF formula is returning 0 for false (default) and the column number for true so MAX is then grabbing the highest column number from the array (8 or "H" in the below example).

INDEX then uses this column number to return the result from that cell in its defined range: INDEX(array, row_num, [column_num]). The row number is always 1 since we are only making the range of cells INDEX is searching in 1 row long and by including column 1 (or "A") in the formula the column number that MAX is calculating will return the correct result.

Hope this was what you were looking for, if you were looking for the last time a cell was modified you can use =CELL("contents") without defining a cell reference and it will behave for the last modified cell but that only works once per sheet, I haven't found a way to make it behave for a single row.