Hi Sandeep,
if you can imagine writing a macro, then you could:
* access all sheets (like "Dim shX As Worksheet: For Each shX In Worksheets")
* and per sheet read all rows (last row can be found like "Dim lngLastRow As Long: lngLastRow = shX.Cells(Rows.Count, 1).End(xlUp).Row 'you can change the column id "1" to any other column id, depending, which is your leading column containing data to be read")
If you want to create normal Excel formulas, you could
* have a self ceated list of sheet names (e.g. in column A)
* have right from this list the respective number of the last row (like "{=MAX(ROW('Sheet1'!A:A)*('Sheet1'!A:A<>""))}") - type the fomula without { / } and with CTRL SHFT ENTER because it is a matrix formula. Substitue "Sheet1" with the respective sheet name.
* have a long list with an index starting with value 1 (2nd entry: 2, then 3,4,5,...)
* in a second column of this list, you check if the list index is <= last row of first sheet. If yes: source is first sheet. if not, check if the list index is <= (last row of first sheet + last row of second sheet). If yes: source is second sheet....
Good luck ;-)
Karsten