UKC

Hoping the knowledge on UKC will help again,

Sorry finding it quite hard to explain what I want to happen!

Basically on excel I want to make a table where the values drop down one slot in the adjacent row, so it would look like this (with words rather than numbers if that helps?)

A 1 then the next row A 3
B 2 B 1
C 3 C 2

And so on, I can do it by a bit of copying and pasting but wondered if there is a simple button to make it happen!

Thanks

In reply to Jonshef:

Not 100% what you are asking, but I think what you are looking for is.

Highlight the cells, click the black box in the bottom right corner, then drag.

Al

In reply to Mr Trebus:

thanks, sorry don't think I'm explaining it very well, that clicking works, but just trying to get the values to change. Does this make it any clearer?

A 1 3 2 1
B 2 1 3 2
C 3 2 1 3

In reply to Jonshef:
So you want the value in A1 to drop 1 row and go 1 row right?
For any cell other than A1 use the following foruma =IF(column()=1,row(),OFFSET(B2,-1,-1))
This assumes rows in column A have values ascending by 1 by each row.

In reply to Jonshef:
If the contents are numbers (as in your example) you could do it by observing that each row follows a repeating 321321321 sequence and using formulas to reproduce this.

However, you can easily achieve the 'dropping down' effect by adding a formula refering to the cell up and to the left. For example, if you type 1,2 and 3 in cells A1,A2,A3 respectively, then in cell B2 you should type =A1

If you then use the black box dragging method to spread this formula down and then across you will get the dropping down effect. What you won't get is the end values 'looping back' to the top. So it will look like:

1
2 1
3 2 1
n 3 2
n n 3

('n' is just there because UKC won't let me have blank spaces)