# Shift cells right function

#### mike_ate_a_pie

##### Board Regular
Hi,

I'm trying to shift cells right depending on a value i enter into another cell.

For eg. Currently I have:

A B C D E F
1 2 3 4 5 6

I would then like to be able to have another row below which would shift the values right appropriately:

Eg. if I enter 6 in a nearby cell

A B C D E F G H I J K L
- - - - - - 1 2 3 4 5 6

I have tried offsets and indirect functions but am unsure how to do this?

Any help would be REALLY appreciated.

#### pgc01

##### MrExcel MVP
Hi Mike

I wrote in row 10 the list of the values that you want to appear in the row below the header row.

In A4:

=IF(COLUMNS(\$A4:A4)<=\$B\$1,"",INDEX(\$A\$10:\$K\$10,COLUMNS(\$A4:A4)-\$B\$1))

Copy to the right

Remark: I assumed that values in the the list in row 10 can be any. If the values are always just the natural sequence then the formula can be simplified.

#### mike_ate_a_pie

##### Board Regular
thats amazing, thanks a million

#### mike_ate_a_pie

##### Board Regular
Thanks again for the post before.

In addition do you know a way where by you could still shift to the right by the entering a number in cell B1 but also determin the gap between the values. For example if i wanted a 4 month start delay then a 1 month gap between values 2 and 3 and 1 month gap between values 4 and 5.

e.g -
Delay = 4 (B2)
1 to 2 = 0 (eg entered in a table near by)
2 to 3 = 1
3 to 4 = 0
4 to 5 = 1
5 to 6 = 0
6 to 7 = 0

This would give:

A B C D E F G H I J K
1 2 - 3 4 - 5 and so on

This would be very impressive if this was possible. Again any help appreciated.

Many thanks

#### pgc01

##### MrExcel MVP
Hi

Can you not define that directly in the list in row 10, leaving the gaps as needed?

If that's ok, you just have to change the formula to avoid zeros in case of the gaps. In A4:

=IF(COLUMNS(\$A4:A4)<=\$B\$1,"",IF(INDEX(\$A\$10:\$L\$10,COLUMNS(\$A4:A4)-\$B\$1)="","",INDEX(\$A\$10:\$L\$10,COLUMNS(\$A4:A4)-\$B\$1)))

#### mike_ate_a_pie

##### Board Regular
that would work, cheers pgc01