Is there a way to replicate downwards from cells across?

**TakeMeAway** · 05-18-2013, 10:03 AM

Is there a way to replicate a row of values (e.g. Cool, Hello, Books) into a column?

I want to replicate it using formulas, so that if I were to change the value of "Cool", the value would change instantly.

And what is circular reference warning? I sometimes get that error and it makes my formulas not work, how do I fix it?

Thanks all!

**Tony Valko** · 05-18-2013, 10:11 AM

Try this...

D1:F1 = Cool, Hello, Books

You also want those entries to appear in the range A1:A3. Enter this formula in A1 and copy down to A3:

=INDEX(D$1:F$1,ROWS(A$1:A1))

A circular reference usually means that a formula is referencing a cell that the formula itself is entered in. A simple example:

Cell A1 contains this formula:

=IF(B1="",A1,B1)

**TakeMeAway** · 05-18-2013, 10:27 AM

Thanks, it really helped! But how do I replicate it across from data downwards? Thanks again!

I don't quite understand how the formula works though.

**Tony Valko** · 05-18-2013, 10:32 AM

If you want it to go from vertical to horizontal

A1:A3 = Cool, Hello, Books

Enter this formula in D1 and copy across to F1:

=INDEX($A1:$A3,COLUMNS($D1:D1))

**TakeMeAway** · 05-18-2013, 10:40 AM

Thanks once again! It works and I can understand where the "$A1:$A3" comes from but where does the $D1:D1 come from?

How does that work?

**Tony Valko** · 05-18-2013, 10:58 AM

Cells A1:A3 are the source range. There are 3 cells in the source range. We can say that cell:

A1 is located in positon 1 of the source range
A2 is located in positon 2 of the source range
A3 is located in positon 3 of the source range

We're entering this formula in cell D1:

=INDEX($A1:$A3,COLUMNS($D1:D1))

We use INDEX to hold the source range:

=INDEX($A1:$A3

Then we have to tell it to return the item located at the position we're interested in.

In this case we use the COLUMNS function:

COLUMNS($D1:D1)

COLUMNS(...) returns the count of columns in the referenced range.

$D1:D1 = 1 column

As we copy the formula across a row the column reference will increment like this:

Entered in cell D1: COLUMNS($D1:D1) = 1
Copied across to E1: COLUMNS($D1:E1) = 2
Copied across to F1: COLUMNS($D1:F1) = 3

So, the formula in:

D1 evaluates to: =INDEX($A1:$A3,1) = return the value from position 1 of the source range A1:A3 = the value of cell A1

E1 evaluates to: =INDEX($A1:$A3,2) = return the value from position 2 of the source range A1:A3 = the value of cell A2

F1 evaluates to: =INDEX($A1:$A3,3) = return the value from position 3 of the source range A1:A3 = the value of cell A3