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

--

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

[John James]

The formula for, say, cell A2 is
=OFFSET(A1,0,A3)

Oops. Should have been:
=IF(ISERROR(OFFSET(A1,0,-$A$3)),"",OFFSET(A1,0,-$A$3))

[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?

"John James" wrote:

>
> The formula for, say, cell E6 is
> =OFFSET(E5,0,A3)
>
>
> --
> John James
> ------------------------------------------------------------------------
> John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> View this thread: http://www.excelforum.com/showthread...hreadid=531737
>
>

[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" wrote:

> I tried both of these formulas suggested and the result is labeled "volatile"
> and not showing any numbers in the cells. Any other suggestions?
>
> "John James" wrote:
>
> >
> > The formula for, say, cell E6 is
> > =OFFSET(E5,0,A3)
> >
> >
> > --
> > John James
> > ------------------------------------------------------------------------
> > John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> > View this thread: http://www.excelforum.com/showthread...hreadid=531737
> >
> >

[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

"Fin Analyst" wrote:

> 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" wrote:
>
> > I tried both of these formulas suggested and the result is labeled "volatile"
> > and not showing any numbers in the cells. Any other suggestions?
> >
> > "John James" wrote:
> >
> > >
> > > The formula for, say, cell E6 is
> > > =OFFSET(E5,0,A3)
> > >
> > >
> > > --
> > > John James
> > > ------------------------------------------------------------------------
> > > John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> > > View this thread: http://www.excelforum.com/showthread...hreadid=531737
> > >
> > >

[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

"Fin Analyst" wrote:

> 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" wrote:
>
> > I tried both of these formulas suggested and the result is labeled "volatile"
> > and not showing any numbers in the cells. Any other suggestions?
> >
> > "John James" wrote:
> >
> > >
> > > The formula for, say, cell E6 is
> > > =OFFSET(E5,0,A3)
> > >
> > >
> > > --
> > > John James
> > > ------------------------------------------------------------------------
> > > John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> > > View this thread: http://www.excelforum.com/showthread...hreadid=531737
> > >
> > >