Cells equal to another cell a certain # over

[Fin Analyst]

Good evening~

I am trying to get a row of cells to equal another row, but an allotted
number over. Say for instance that I had a values in row 1 and I want row 2
to be equal to row 1 except 7 columns to the right. I.e. the value in A1
would appear in H2. The problem I have is how do I do this is the number of
columns is variable and depends on a number in A3. The number in A3 ranges
from 1-15. How do I write a formula to account for a variable number of
columns such as this? I can do this if the number of columns is static, but
I'm at a loss on how if it is variable. Can somebody please help?

Thanks,
Jaclyn

 

[Bryan Hessey]

In A2 put

=IF(COLUMN()<=$A$3,"",IF(OFFSET(A1,0,-$A3)="","",OFFSET(A1,0,-$A3)))

and formula-drag that to the right

--

 

[John James]

The formula for, say, cell E6 is
=OFFSET(E5,0,A3)

 

[Fin Analyst]

I tried both of these formulas suggested and the result is labeled "volatile"
and not showing any numbers in the cells. Any other suggestions?

 

[Fin Analyst]

I'm sorry, I forgot the file is on manual calculate. Once I hit F9 the
formula Bryan gave me works great. I do have one probelem though. The
information I want actually starts in H1. There is information in the
columns in front including a row description and a total. The cells A1
through E1 are blank. I don't want this information in F1 and G1 showing up.
Is there any way to disinclude them from the offset formula result? Thanks
for the help thus far!

Jaclyn

 

[Fin Analyst]

Nevermind, I figured it out. Bryan, thank you so much for the formula. All
I had to do was add 7 to the column number, so it doesn't pick up the row
description or total amount. So simplistic, but I didn't have time to
analyze it earlier. Here is my final formula in case anybody encounters a
similar problem:

IF(COLUMN()<=($C$111+7),"",IF(OFFSET(I8,0,-$C111)="","",OFFSET(I8,0,-$C111)))

C111 is the number of columns I need to offset by
row 8 is the information I am pulling from

Thanks again for all the help!!

Jaclyn

 

[Bryan Hessey]

Good to see, and thanks for the response

--

Nevermind, I figured it out. Bryan, thank you so much for the formula.
All
I had to do was add 7 to the column number, so it doesn't pick up the
row
description or total amount. So simplistic, but I didn't have time to
analyze it earlier. Here is my final formula in case anybody
encounters a
similar problem:

IF(COLUMN()<=($C$111+7),"",IF(OFFSET(I8,0,-$C111)="","",OFFSET(I8,0,-$C111)))

C111 is the number of columns I need to offset by
row 8 is the information I am pulling from

Thanks again for all the help!!

Jaclyn