Create a mirror image of a range of cells

[DavidC1970]

Hello Experts,

I have a table with a range of cells that I want to create a mirror image of. So for example :-

If i have a range of cells from cell A:1 through to A:8 with the following data in 1 2 3 4 5 6 7 8 I want to mirror these cells from cell A:9 through to A:16 with the values 8 7 6 5 4 3 2 1

Is this possible? I

Many thanks in advance

 

[Dan_W]

So there are doubtless more elegant ways of writing it, but the following formula works. It needs to be put in as the formula for A9, and then dragged down to cell A16. You will see Cell A9 (the first cell in the mirrored range) has the $ sign, this means that it is an absolute reference or is 'locked'. If you later need to use this formula elsewhere, you should change the first cell and make sure it is locked in the same manner - so if the data is C1 to C20 and you want to mirror from Cell C21 onwards, you just need to swap the $A$9 for $C$20 and the A9 for C20, so:

=OFFSET($C$20,ROW($C$20)-(ROW(C20)+1),,)

Book1
	A	B	C	D	E	F
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	8		=OFFSET($A$9,ROW($A$9)-(ROW(A9)+1),,)
10	7
11	6
12	5
13	4
14	3
15	2
16	1
Sheet2

Cell Formulas
Range	Formula
A9:A16	A9	=OFFSET($A$9,ROW($A$9)-(ROW(A9)+1),,)

 

[maabadi]

You see two method at column A & B

Book1
	A	B	C	D
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	8	8
10	7	7
11	6	6
12	5	5
13	4	4
14	3	3
15	2	2
16	1	1
17
18
Sheet1

Cell Formulas
Range	Formula
A9:A16	A9	=INDIRECT("A" & 17-ROW())
B9:B16	B9	=INDEX($A$1:$A$8,16-ROWS($A$1:A8))

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option depending on your version

+Fluff 1.xlsm
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	8
10	7
11	6
12	5
13	4
14	3
15	2
16	1
17
Main

Cell Formulas
Range	Formula
A9:A16	A9	=SORTBY(A1:A8,ROW(A1:A8),-1)
Dynamic array formulas.