Excel 2007 - formula to display last updated cells

Question

I have 6 cells, A1-A6 in Sheet1.

A1,A3,& A5 contain numbers 1-3

A2,A4,& A6 contain the date the numbers changed.

A1 & A2 will be filled first. I want to display both cells in Sheet2 in B1 & B2.

When A3 & A4 are filled, I want to replace the contents of B1 & B2 with the new data.

Similarly, when A5 & A6 are filled I want to replace the contents of B1 & B2 with the new data.

I need to keep all the data in Sheet1 but only want the latest data displayed in Sheet2.

Is there a formula that can do this as I need it automatically? I am unable to use VBA or macros to do this.

Answer 1

A) It's usually good form to show what you've tried

B) this would have probably been better posted on Stack Overflow.SE

I have set up Column A with these 6 rows:

1
2/1/2015
2
2/14/2015
3
3/1/2015

Based on that, I put these formulas in other cells on the same sheet

'return the odd numbered row (1, 2 or 3)
=INDEX($A$1:$A$6,MATCH(MAX($A$2,$A$4,$A$6),$A$1:$A$6,0)-1)
'return the associated date
=INDEX($A$1:$A$6,MATCH(MAX($A$2,$A$4,$A$6),$A$1:$A$6,0))

You'll need to change the cell references to point to the appropriate sheet.

Answer 2

In Sheet2 cell B1 enter:

=CHOOSE(1+COUNT(Sheet1!A1:A6)/2,"",Sheet1!A1,Sheet1!A3,Sheet1!A5)

and in cell B2 enter:

=CHOOSE(1+COUNT(Sheet1!A1:A6)/2,"",Sheet1!A2,Sheet1!A4,Sheet1!A6)

These formulas assume you are filling Sheet1 in pairs and in the order indicated.