Consolidating info from multiple lists

[Marika Pieterse]

I have multiple sheets containing data. I need to consolidate these into a single list.

sheet 1 - name AAA contains 700 records but records can be added or deleted any time
sheet 2 - name BBB contains 199 records but records can be added or deleted any time
sheet 3 - name CCC contains 803 records but records can be added or deleted any time

I have set-up an INDEX sheet where each sheet's name and the count of records appear. I used =INDEX(A3:A1700,COUNT(A3:A1700)) to read the number of records for each sheet. This is then picked up in the INDEX sheet.

In the COMBINED sheet, I have a sequence nr that is the count (from 1 to n) for the sheet.
I then use the following formula =IF(ROWS($C3:D3)<=$B3,SMALL(IF($B3>=AAA!$A$3:$A$1700,IF($B3<=AAA!$A$3:$A$1700,ROW(AAA!$A$3:$A$1700)-ROW(AAA!$A$3)+1)),ROWS($C3:D3)),"") to pick up the row number for the sequence number of the corresponding row in the sheet.
I then use the formula =IF(N($D3),INDEX(AAA!$A$3:$CC$1700,$D3,MATCH(E$2,AAA!$A$2:$CC$2,0)),"") to pick up the content of the cells that I need from the individual sheets.

The problem is that I need to update the COMBINED sheet dynamically as rows are added or deleted, but I don't know
(1) how to determine the sheet name in the formula from another cell in the combined sheet
(2) how to tell excel that it must continue with the sequence number until it has reached the number on the index (i.e. for AAA = 700 records) and then start with the BBB sheet's data and so on

Should I write a macro? Any assistance would be great!