concatenating rows from different sheets

E

ExcelWolfie

I have 2 worksheets each with an unknown number of rows which I want to move
to the third worksheet as follows

Sheet 1 data in rows 1 to 15
Sheet 2 data in rows 1 to 23
Sheet 3 to contain Sheet 1 rows 1-15 in rows 1 - 15 with
sheet 2 rows 1 - 23 in sheet 3 rows 16 to 38
Automatically if possible but I don't know how many rows will be in each of
sheets 1 and 2
 
V

vezerid

You can use this formula, which is assuming that the data will be in
column A:A in both sheets 1 and 2 (assuming sheets are called Sheet1
and Sheet2)

=IF(ROW()>COUNTA(Sheet1!A:A)+COUNTA(Sheet2!A;A),"",
IF(ROW()<=COUNTA(Sheet1!A:A),
OFFSET(Sheet1!$A$1,ROW()-1,0),OFFSET(Sheet2!$A$1,ROW()-COUNTA(Sheet1!A:A)-1,0)))

Start this formula in A1 of Sheet3 and copy down until you get empty
cells.

Does this help?
Kostis Vezerides
 
Top