Copying cell contents to another cell contingent upon there being data in the cell below

Hi everyone. I'm trying to set up excel so that if there is any data in the cell directly below the cell in question, the contents of the above cell get copied to another. Also, any cell with data to the furthest right takes precedence.

So, for example, if there's any data in cell A2, I want the contents of the cell above it (in this case, A1) to be copied to another cell, such as R1. What's needed though is that only the rightmost cell with data has the cell above it copied to another.

So, if there's data in A2, A1 copies to R1, but if there data in cell B2 then B1 gets copied to R1, and if there's data in C2 then C1 gets copied to R1, and so on..

Can anyone give me a hand with this? Thanks!

little code in Worksheet_Chance event

Please Login or Register  to view this content.

Kind regards
Leo

Ok, I've attached an example file, which is just one sheet I cut out of my main file (with the business name changed). It's basically a calendar/ticket system. In cell AF9 is this formula:

=LOOKUP(2, 1/($A9:$AE9<>""), $A9:$AE9)

What it does is take the rightmost cell in that row with any content in it and displays it in cell AF9.

As you can see, cell J9 has the word 'no' in it. As a result, the word 'no' is then displayed in cell AF9. If anything is put into any cell to the right of this cell (such as K9 - AE9), then that content becomes what is displayed in cell AF9.

What I want is for the date above it to be copied to cell AF8. So in this case, because Aug - 10 in cell J8 is above cell J9 with content in it, the contents of cell J8 (in this case, 'Aug -10') get copied to cell AF8 (so that AF8 has 'Aug - 10' in it).

If N9 has content in it, then I want the 'Aug -14' in cell N8 right above it to be copied to AF8. If cell R9 has content in it, I want the 'Aug - 18' in the cell above it to be copied to cell AF8, and so on..

in AF8

Please Login or Register  to view this content.

kind regards
Leo

Many thanks, Mr. LeoTaxi! This does the trick. Thank you!