software

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


In A2 put

=IF(COLUMN()lt;=$A$3,quot;quot;,IF(OFFSET(A1,0,-$A3)=quot;quot;,quot;quot;,OFFSET(A1,0,-$A3)))

and formula-drag that to the right

--

Fin Analyst Wrote:
gt; Good evening~
gt;
gt; I am trying to get a row of cells to equal another row, but an
gt; allotted
gt; number over. Say for instance that I had a values in row 1 and I want
gt; row 2
gt; to be equal to row 1 except 7 columns to the right. I.e. the value in
gt; A1
gt; would appear in H2. The problem I have is how do I do this is the
gt; number of
gt; columns is variable and depends on a number in A3. The number in A3
gt; ranges
gt; from 1-15. How do I write a formula to account for a variable number
gt; of
gt; columns such as this? I can do this if the number of columns is
gt; static, but
gt; I'm at a loss on how if it is variable. Can somebody please help?
gt;
gt; Thanks,
gt; Jaclyn--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531737
The formula for, say, cell E6 is
=OFFSET(E5,0,A3)--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531737I tried both of these formulas suggested and the result is labeled quot;volatilequot;
and not showing any numbers in the cells. Any other suggestions?

quot;John Jamesquot; wrote:

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

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

quot;Fin Analystquot; wrote:

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

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()lt;=($C$111 7),quot;quot;,IF(OFFSET(I8,0,-$C111)=quot;quot;,quot;quot;,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

quot;Fin Analystquot; wrote:

gt; I'm sorry, I forgot the file is on manual calculate. Once I hit F9 the
gt; formula Bryan gave me works great. I do have one probelem though. The
gt; information I want actually starts in H1. There is information in the
gt; columns in front including a row description and a total. The cells A1
gt; through E1 are blank. I don't want this information in F1 and G1 showing up.
gt; Is there any way to disinclude them from the offset formula result? Thanks
gt; for the help thus far!
gt;
gt; Jaclyn
gt;
gt; quot;Fin Analystquot; wrote:
gt;
gt; gt; I tried both of these formulas suggested and the result is labeled quot;volatilequot;
gt; gt; and not showing any numbers in the cells. Any other suggestions?
gt; gt;
gt; gt; quot;John Jamesquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; The formula for, say, cell E6 is
gt; gt; gt; =OFFSET(E5,0,A3)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; John James
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=531737
gt; gt; gt;
gt; gt; gt;


Good to see, and thanks for the response

--

Fin Analyst Wrote:
gt; Nevermind, I figured it out. Bryan, thank you so much for the formula.
gt; All
gt; I had to do was add 7 to the column number, so it doesn't pick up the
gt; row
gt; description or total amount. So simplistic, but I didn't have time to
gt; analyze it earlier. Here is my final formula in case anybody
gt; encounters a
gt; similar problem:
gt;
gt; IF(COLUMN()lt;=($C$111 7),quot;quot;,IF(OFFSET(I8,0,-$C111)=quot;quot;,quot;quot;,OFFSET(I8,0,-$C111)))
gt;
gt; C111 is the number of columns I need to offset by
gt; row 8 is the information I am pulling from
gt;
gt; Thanks again for all the help!!
gt;
gt; Jaclyn
gt;
gt; quot;Fin Analystquot; wrote:
gt;
gt; gt; I'm sorry, I forgot the file is on manual calculate. Once I hit F9
gt; the
gt; gt; formula Bryan gave me works great. I do have one probelem though.
gt; The
gt; gt; information I want actually starts in H1. There is information in
gt; the
gt; gt; columns in front including a row description and a total. The cells
gt; A1
gt; gt; through E1 are blank. I don't want this information in F1 and G1
gt; showing up.
gt; gt; Is there any way to disinclude them from the offset formula result?
gt; Thanks
gt; gt; for the help thus far!
gt; gt;
gt; gt; Jaclyn
gt; gt;
gt; gt; quot;Fin Analystquot; wrote:
gt; gt;
gt; gt; gt; I tried both of these formulas suggested and the result is labeled
gt; quot;volatilequot;
gt; gt; gt; and not showing any numbers in the cells. Any other suggestions?
gt; gt; gt;
gt; gt; gt; quot;John Jamesquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; The formula for, say, cell E6 is
gt; gt; gt; gt; =OFFSET(E5,0,A3)
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; John James
gt; gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; John James's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32690
gt; gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=531737
gt; gt; gt; gt;
gt; gt; gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531737