Alternate cell data based on different Spreadsheet data

**R_S_6** · 07-28-2008, 08:46 AM

Hello all, Im new to the forum :-)

Sorry for the slightly non-descriptive title, but i am struggling to describe what i want excel to do: so here goes;

I have a file with 3 spreadsheets in; Spreadsheets 1, 2 and 3.

In Spreadsheet 1 I have data listed in column A from row 1 to 5 for example.
A1 - 10
A2 - 200
A3 - 13
A4 - 5
A5 - 600

In spreadsheet 2 I have data listed again in rows 1 to 5 of column A, as below:
A1 - Apples
A2 - Pears
A3 - Oranges
A4 - Lemon
A5 - Lime

What i am trying to do in Spreadsheet 3 is to alternatly link the data from spreadsheet 1 and 2 to column A of spreadsheet 3. See example below of spreadsheet 3:

A1 - 10 [Spreadsheet1!A1]
A2 - Apples [Spreadsheet2!A1]
A3 - 200 [Spreadsheet1!A2]
A4 - Pears [Spreadsheet2!A2]
A5 - 13 [Spreadsheet1!A3]
A6 - Oranges [Spreadsheet2!A3]
A7 - 5 [Spreadsheet1!A4]
A8 - Lemon [Spreadsheet2!A4]
A9 - 600 [Spreadsheet1!A5]
A10 - Lime [Spreadsheet2!A5]

Sorry for the rather poor example. I dont know if there is formula or VBA that i can use to do the above task. I need this to work with hundreds of rows in both spreadsheets 1 and 2 idealy.

Thanks in Advance.

PS Using Excel 2003

**R_S_6** · 07-28-2008, 08:51 AM

Thought it may help if i attached the above example file. Bearing in mind that i have manually linked each of the cells in column A of spreadsheet 3.

Cheers

**Ron Coderre** · 07-28-2008, 09:20 AM

Using your sample workbook...
Try this on Sheet3...

Please Login or Register  to view this content.

Select A1:A2
Left-Click and HOLD on the A2 fill handle (black box in the lower right of the cell)
and drag down as far as you need.

Does that help?

**R_S_6** · 07-28-2008, 09:27 AM

Magic - That works great thank you!!

A couple of questions:

1.Could you explain the formula, for instance the 'CEILING(ROW()/2,1)'

2.When i drag the cells down i get a bloack surround around the cells from Sheet2, the ones containg text? Is this normal?

Thank you ever so much for your help. I really appreciate your speedy and consice response!

Cheers

**Ron Coderre** · 07-28-2008, 09:41 AM

Regarding:

Please Login or Register  to view this content.

The INDEX(rng, x, y) function references a range and returns the contents
of the cell located in the x,y position in that range,
where:
x = vertical position in that range
y = horizontal position in that range

Since Sheet1!$A:$A only references a single column,
we only need to identify the vertical postion: INDEX(rng, x)

The CEILING(expression, value) function rounds the expression UP to the
next multiple of value. In this example, the ROW() function returns the
row_number of the cell containing the formula.

The CEILING(ROW()/2,1) function divides the row_number by 2 and rounds
that value UP to the next increment of 1.

Examples:
CEILING(1/2,1) returns: 1
CEILING(2/2,1) returns: 1
CEILING(3/2,1) returns: 2
CEILING(4/2,1) returns: 2
etc

Consequently, every other row increments the referenced range by 1.

I hope that helps.