Is there a way of working in Loops in Excel..?

[all4excel]

Changing reference after every 6th column

The first Row 1 contains the main Parameter which in this case is "Population"..
Population is further divided into Mileage and Driving Distance.

Mileage is further divided into 3 sub-categories
5 miles, 10 miles and 15 miles..

Driving Distance is further divided into 3 sub-categories
5 minutes, 10 minutes and 15 minutes..

I have merged the cells from $D$1 till $I$1 for a better view.

However i want to use the information in the Row 4 starting from D4 in some other sheet...

I want some formula which can refer to D1 to produce the result as
Population - 5 miles, Population - 10 miles, Population - 15 miles , etc..

I want to avoid using the 2 rows just to make this possible...

I have added Call-outs whic explains in the attachment..

[Bjornar]

This formula can be put in any cell in column D and copied to the other colums to give you the result you want.

Please Login or Register  to view this content.

If you are to use this in some other part of your sheet and start in another column than D you have to adjust the number +2 (undelined) in the formula to calculate the correct colum. Eg: +1 if you start in column E, and +3 if you start in column C.

=OFFSET(D1,0,-MOD(COLUMN()+2 ,6),1,1)&" - "&D3

[all4excel]

This formula can be put in any cell in column D and copied to the other colums to give you the result you want.

Please Login or Register  to view this content.

If you are to use this in some other part of your sheet and start in another column than D you have to adjust the number +2 (undelined) in the formula to calculate the correct colum. Eg: +1 if you start in column E, and +3 if you start in column C.

=OFFSET(D1,0,-MOD(COLUMN()+2 ,6),1,1)&" - "&D3

Thanx for not only the help but also for the explanation....
God bless you..

[all4excel]

Is it possible to avoid having that 4th row where i type

5 miles,10 miles , 15 miles

5 minutes,10 minutes , 15 minutes.

if u observe it remains constant throughout the entire row...

Is that possible...

[all4excel]

Is there a way of working in Loops..

Like in the following scenario....

I want a series of 5 miles, 10 miles , 15 miles , 5 minutes , 10 minutes , 15 minutes then again

5 miles, 10 miles , 15 miles , 5 minutes , 10 minutes , 15 minutes..

So after every iteration of 6 the exact value gets repeated...

The values in the cells are as follows.

CELL D4 - 5 MILES
CELL E4 - 10 MILES
CELL F4 - 15 MILES
CELL G4 - 5 MINUTES
CELL H4 - 10 MINUTES
CELL I4 - 15 MINUTES

Then the cycle repeats again from cell j4

CELL J4 - 5 MILES
CELL K4 - 10 MILES
CELL L4 - 15 MILES
CELL M4 - 5 MINUTES
CELL N4 - 10 MINUTES
CELL O4 - 15 MINUTES

SO on so forth...

[Simon Lloyd]

I don't know how many iterations you require over how far but if you enter your text in a row in seperate cells then once you have done that highlight the cells you will see a small black square (drag handle) at the bottom right of your selection, position your mouse over that and hold down Ctrl you should now see two +'s one smaller than the other, keeping Ctrl held down and your left mouse button drag the content for as far as you need.
Hope this helps!

[mikerickson]

Regarding the question in post #5:
In D4 and all cells right

=CHOOSE(MOD(COLUMN(),6)+1,"15 miles","5 minutes","10 minutes","15 minutes","5 miles","10 miles")

[all4excel]

This formula can be put in any cell in column D and copied to the other colums to give you the result you want.

Please Login or Register  to view this content.

If you are to use this in some other part of your sheet and start in another column than D you have to adjust the number +2 (undelined) in the formula to calculate the correct colum. Eg: +1 if you start in column E, and +3 if you start in column C.

=OFFSET(D1,0,-MOD(COLUMN()+2 ,6),1,1)&" - "&D3

Thanx a lot for the help

[all4excel]

Regarding the question in post #5:
In D4 and all cells right

=CHOOSE(MOD(COLUMN(),6)+1,"15 miles","5 minutes","10 minutes","15 minutes","5 miles","10 miles")

Excellent -Compact code

Thanx a lot for the help