0

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.

0

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.

  • (A) I agree. (B) No, Excel questions that can be answered without VBA are on-topic on Super User; see Please don't vote to close Excel questions as off-topic. Even questions that require limited VBA are generally allowed. In case of a gray area, the general rule is that a question belongs on whatever site the asker chooses to put it on. – Scott Mar 13 '15 at 19:52
  • (B) Roger that, @Scott. – FreeMan Mar 13 '15 at 19:55
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.