Can I use "Fill Down" to make a vertical series reference a horizontal series?

Question

Say I have a data series going from A1 to E1. I would like to make a second copy of these cells going vertically, for instance from G1-G5. I can achieve what I want just by typing =A1 into G1, =B1 into G2 and so on. However, I have in mind a situation in which this needs to be done with a larger data series. I'm keen to economize on keystrokes if I can.

Is there a way to use Fill or some other command to achieve this?

Solution 1

You can use a formula, e.g. in G1 copied down to G5

=INDEX(A$1:E$1,ROWS(G$1:G1))

Solution 2

Paste Special-Transpose should work for you.

Here is an example of what this does (which is exactly what you are looking for)

Edit

I think you are looking for this trick -

1. Select G1-G5.
2. In the formula bar (you know the one that shows you the formula instead of the value), type in =Transpose(A1:E1) and use ctrl+shift+enter to complete your edit.

Cheers.

Comments

[LA Developers]

Thanks. I thought of that, but when I paste special transpose (even if I choose the 'Formulas' option) I get the value from A1 in G1, the value of B2 in G2, and so on. What I really want is for the formula =A1 to be in G1. This is because later on the value in A1 will change.

[Jerry Beaucaire]

This is my preferred technique. But as with all formulas like this, be aware that CUTTING rows or columns within the noted ranges may affect the remaining results, watch for that and reenter the formulas to reset them if you do much of that.

[Ben Jones]

I edited my answer. let me know if that works for you.