Fill a series horizontally based a vertical data set

LarryM

Board Regular
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

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

Replies
4
Views
154
Replies
9
Views
379
Replies
14
Views
275
Replies
1
Views
44
Replies
2
Views
96