Cells equal to another cell a certain # over
 

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

Cells equal to another cell a certain # over
 

In A2 put

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

and formula-drag that to the right

--

Fin Analyst Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531737

Cells equal to another cell a certain # over
 

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

Cells equal to another cell a certain # over
 

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

Cells equal to another cell a certain # over
 

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

Cells equal to another cell a certain # over
 

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

Cells equal to another cell a certain # over
 

Good to see, and thanks for the response

--

Fin Analyst Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531737