Append Tables in different worksheets using Formulas

[noboffinme]

Hi Forum,

I've found a good Formula for appending 2 Tables that are on different worksheets from the site on the below link.

I was wondering if it can be altered to append any number of tables (about 20) & not just 2.

http://superuser.com/questions/53733...ntical-columns

I've attached a file that follows the link instructions where 2 tables of data - Sheet1 & Sheet2 are appended on Sheet3.

Sheet1 & Sheet2 have Named Ranges (SQLDB, EXCELRNG Respectively) that are used in the Formula on Sheet3.

Here's the Formula on Sheet3 that brings the Sheet1 & 2 data over to Sheet3.

Please Login or Register  to view this content.

I've created another table on Sheet4 to see if anyone can add this into the Formula so it also appends on Sheet3.

Hope I've explained this clearly.

Thanks

[noboffinme]

Hi,

I tried a quick test to open my Excel attachment & found it wanted to open as a php file??

If this happens, save it as an .xlsx file & it should work ok. Thanks

[JohnTopley]

Try

=IFERROR(INDEX(SQLDB,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(EXCELRNG_1,ROWS(A$2:A2)-ROWS(SQLDB),COLUMN(A2)),IFERROR(INDEX(EXCELRNG_2,ROWS(A$2:A2)-ROWS(SQLDB)-ROWS(EXCELRNG_1),COLUMN(A2)),"-")))


Good luck with 20 sheets!!!

You might look at the attached which offers another approach:

[noboffinme]

Thanks John,

That's perfect!

Much appreciated!

[JohnTopley]

Thank you for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[noboffinme]

Funny, No-One has ever brought up the 'Thread Tools' feature to class a Post as Solved.

I didn't know how it was done. Thanks again. : )