Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two 'tabs' in my Excel file and I would like to merge the two tabs into one sheet (in the same file).

The two files look somthing like:

artnr      language       description      price
artnr      language       description      price
artnr      language       description      price

What I would like to do is merge those two files in the following manner:

first row of first sheet
first row of second sheet
second row of first sheet
second row of second sheet
etc.

I tried to use the following formula:

=Sheet1!A1
=Sheet2!A1
=Sheet1!A2
=Sheet2!A2

This works, however when I try to expand the formula down (to the other rows) I get:

=Sheet1!A1
=Sheet2!A1
=Sheet1!A2
=Sheet2!A2
=Sheet1!A5
=Sheet2!A5

In stead of:

=Sheet1!A1
=Sheet2!A1
=Sheet1!A2
=Sheet2!A2
=Sheet1!A3
=Sheet2!A3

Any help is appreciated!

share|improve this question
up vote 4 down vote accepted

If you do not require the referencing to the original cells, you could do this another way.

First, in sheet 1 row 1; enter odd numbers on the cell to the right starting with 1 and drag the pattern to the last row

first row | 1
second row | 3
third row | 5
...

...and the same for sheet 2; except you use even numbers starting with 2.

Finish off by copying sheet 1 rows (including the numbering) below sheet 2 rows and do a sort by numbering column.

share|improve this answer
    
Now that's a simple and working solution! Thanks! – PeeHaa Dec 28 '10 at 13:47
    
Glad I could help. – Reuben L. Dec 28 '10 at 13:59

Put the following formula in A1 of your third sheet:

=IF(ROW(A1)/2=INT(ROW(A1)/2);Sheet2!A1;Sheet1!A1)

and then copy&paste in the rows below.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .