Fill a series horizontally based a vertical data set

[LarryM]

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

 

[Domenic]

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]

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]

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]

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!