This may work for you. I created tables on Sheet2 and Sheet3 and on Sheet1 I have them combined.
There are several formulae involved to number the rows on each worksheet consecutively.
Eg. Sheet2!A2 this is used to produce consecutive numbers when there is data in column Sheet2 column B.
A1 of the worksheets that follow take the max value from column A of the previous worksheet.
Sheet3!A1
A small table is created to show the worksheets and the max value of column A of each worksheet.
|
Q |
R |
1 |
|
0 |
2 |
Sheet2 |
=MAX(INDIRECT("'"&Q2&"'!a:a")) |
3 |
Sheet3 |
=MAX(INDIRECT("'"&Q3&"'!a:a")) |
Sheet1!A2 has the following formula which is filled down column A and this copies the worksheet names the appropriate number of times according to the max number. Sheet2 is easy as it is the max of A:A but Sheet 3 number of rows is the max number - the max of the previous worksheet. This formula takes that into consideration with the MATCH function.
With the sheet names now in column A of Sheet1, the values for each worksheet need to be retrieved. I have used VLOOKUP that makes use of the sheet name in column A.
The VLOOKUP value is determined by the ROWS function that starts counting up from 1 with the argument ($1:1). The table array being addressed is established by the value in A2 and the range A2:E15. The INDIRECT function takes "'"&$A2&"'!a2:e15" and makes it a useable range that Excel understands as the Sheet name and cell range. The COLUMNS function is another counter for the column being returned. $A$1:B1 is 2 and as the formula is filled across, this value changes to $A$1:C1 and $A$1:D1 etc. to return the values from the incremented column.
Bookmarks