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

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?

user1205901 - Reinstate Monica

Posted 2012-02-27T05:35:43.353

Reputation: 725

Answers

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

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

barry houdini

Posted 2012-02-27T05:35:43.353

Reputation: 10 434

1This 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. – Jerry Beaucaire – 2012-02-27T14:43:09.100

~~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 -

Select G1-G5.
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.

Ben Jones

Posted 2012-02-27T05:35:43.353

Reputation: 509

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. – user1205901 - Reinstate Monica – 2012-02-27T09:01:07.947

1I edited my answer. let me know if that works for you. – Ben Jones – 2012-02-28T00:37:32.437