Fill a series horizontally based a vertical data set

LarryM

Board Regular
Joined
Dec 27, 2005
Messages
57
I want to fill a series of formulas horizontally but the data set is in a vertical format. For example, I have "objectives" going from A1 to A10 and "actuals" going from A15 to A25. I want to create a horizontal row from cells A50 to J50 that contain division formulas of the data set. So, I create a formula such as A1/A15 in cell A50 and want to copy/paste it over to J50 and have J50 become A15/A25.

I've seen a way to do this but can't recall how's it was done. Is it a trick of some kind? I hope my description above is clear.

Thanks to anyone who can offer any help on this.

Larry
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
Hi Larry! Welcome to the Board!

Assuming that A15:A25 should actually be A15:A24, and that A15/A25 should be A10/A24, try...

A50, copied across:

=INDEX($A$1:$A$10,COLUMNS($A$50:A50))/INDEX($A$15:$A$24,COLUMNS($A$50:A50))

Hope this helps!
 

LarryM

Board Regular
Joined
Dec 27, 2005
Messages
57
That's the function I saw and that did it! Thanks very much. I wish I understand the COLUMNS($A$50:A50) part. If you're in A50 - J50, why do we need this?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
The COLUMNS function returns the number of columns within the specified range. So, if we have...

COLUMNS($A$50:A50)

...the formula returns 1. As you drag the formula across, the reference changes to...

COLUMNS($A$50:B50) ---> 2

COLUMNS($A$50:C50) ---> 3

COLUMNS($A$50:D50) ---> 4

...and so on. The numbers returned by the COLUMNS function are then used as an argument for the INDEX function.
 

LarryM

Board Regular
Joined
Dec 27, 2005
Messages
57
Makes perfect sense now. Thanks again. I guess I was confuse about $50 since all we really need is COLUMNS($A:A).

Again, thanks for the reply and especially for the solution!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,676
Messages
5,573,603
Members
412,538
Latest member
mrjack
Top