Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenating rows from different sheets
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenating rows from different sheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
auto expand rows | Excel Worksheet Functions | |||
Rows & Columns in Excel | Excel Worksheet Functions | |||
Updating, Deleting and inserting rows over two Excel Sheets | Excel Worksheet Functions | |||
REALLY miussing rows in Excel2000 | Excel Discussion (Misc queries) |