[I'm using Excel Pro 2013]

I have a sheet called '11', where I have a table in cells P41:S43

```
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
```

From sheet '12' I want to reproduce the same table (as in if I modify the table in '11', it gets updated in '12'). One way of doing this is manually, by using a formulas such as:

```
='11'!P41 ='11'!Q41 ='11'!R41
='11'!P42 ='11'!Q42 ='11'!R42
='11'!P43 ='11'!Q43 ='11'!R43
```

There are many other sheets (from 1:11) with each having a different table in the same cells (P41:S41). I want to have a sheet called 'All tables' where I have a big table with all individual tables put together. For tables 1:4 in 'All tables' sheet would look like this

```
1 ='1'!P41 ='1'!Q41 ='1'!R41 ='1'!S41
1 ='1'!P42 ='1'!Q42 ='1'!R42 ='1'!S42
1 ='1'!P43 ='1'!Q43 ='1'!R43 ='1'!S43
1 ='1'!P44 ='1'!Q44 ='1'!R44 ='1'!S44
2 ='2'!R38 ='2'!S38 ='2'!T38 ='2'!U38
2 ='2'!R39 ='2'!S39 ='2'!T39 ='2'!U39
2 ='2'!R40 ='2'!S40 ='2'!T40 ='2'!U40
2 ='2'!R41 ='2'!S41 ='2'!T41 ='2'!U41
3 ='3'!Q40 ='3'!R40 ='3'!S40 ='3'!T40
3 ='3'!Q41 ='3'!R41 ='3'!S41 ='3'!T41
3 ='3'!Q42 ='3'!R42 ='3'!S42 ='3'!T42
3 ='3'!Q43 ='3'!R43 ='3'!S43 ='3'!T43
4 ='4'!P38 ='4'!Q38 ='4'!R38 ='4'!S38
4 ='4'!P39 ='4'!Q39 ='4'!R39 ='4'!S39
4 ='4'!P40 ='4'!Q40 ='4'!R40 ='4'!S40
4 ='4'!P41 ='4'!Q41 ='4'!R41 ='4'!S41
```

Which in practice I achieved by writing always the first line of each group and then dragging the formulas down three rows e.g.,

```
='1'!P41 ='1'!Q41 ='1'!R41 ='1'!S41
```

When I would done with the 4 first rows, I would have to change the first row from referencing sheet '1' to sheet '2' and then dragging down three rows (for achieving rows 5-8).

Is there any way where I can achieve the same result (all tables from sheets in one super table) without so much manual work? My idea would be something like this:

```
=INDIRECT(CONCATENATE("'";A2;"'!P41"))
=INDIRECT(CONCATENATE("'";A2;"'!Q41"))
=INDIRECT(CONCATENATE("'";A2;"'!R41")
=INDIRECT(CONCATENATE("'";A2;"'!S41")
```

(imagine those formulas in the same row)

with A2=1 (as you drag down the formula its value will be 1, 2, 3 or 4 depending on the table position. It represents the sheet name taken from the first row)

Problem is that when using this formula, dragging down won't change the reference from P41:S41 cells. Is there any way I can have a formula were dragging down the a row gets me P42:S42 from P41:S41 and so on?

Ignore the INDIRECT + CONCATENATE suggestion if there's an easier way to go about doing this.

`ROW()`

function to increment the row number, and`COLUMN()`

to increment the column number. In row 1,`=ROW()+40`

returns 41. You can convert the column number to a letter with CHAR(). In Column H,`=CHAR(COLUMN()+64)`

returns "H". Hint: capitol A is 65 and the numbers increase through 90 for Z. Finally, I suggest using "&" in place of CONCATENATE(). In H1,`=CHAR(COLUMN()+64)&ROW()+40`

returns "H41". If you fill that to the right and down, it will give I41, J41...I42,J42, etc. – Bandersnatch Nov 15 '17 at 17:25