0

[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.

  • INDIRECT is volatile and a sheet full of them will start to slow down the calculation times. If you go this route I would make sure to put calculations on Manual as any change to any of the table will cause a full recalc of the full summary page and your users will get frustrated. – Scott Craner Nov 15 '17 at 14:43
  • You can use the 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
  • Y don't you use the Power Query,,!! – Rajesh S Nov 19 '17 at 10:16
0

If you have identical tables in different worksheets with same Data Range, the below written Formula can help to Merge Tables.

=IFERROR(INDEX(Comb1,ROWS(A$2:A2),COLUMN(A2)), IFERROR(INDEX(Comb2,ROWS(A$2:A2)-ROWS(Comb1),COLUMN(A2)), "-"))

Before use the Formula in New Sheet, copy the Header Row then place Cell Pointer in A2 Cell, write Formula & execute it then drag it right then down till it's needed.

NB: Comb1 & Comb2 are two Tables.

Hope this help you. I've posted the solution after been tested by me.

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.