I have values in row 2, say in columns A through Z (so cells A2 thru Z2)
for values that happen in months 1 thru 26, respectively. In row 3, I would
like to see those same values appear, but delayed by "N" months. Let's say
N is the value of cell A1, a value that may change from time to time. So,
for example, if cell A2 has the value 4 in it, and if N=3, I would like cell
D3 to have the value 4 in it also.

I have seen this done with an offset function, but it doesn't seem to be
working for me. Can someone tell me what my equations in row 3 need to be?
Do I need to use a range name?

Thanks!
Dean

Perhaps you mean something like this in A3:
=IF($A$1="","",IF(ISERROR(OFFSET(A2,,-$A$1)),"",OFFSET(A2,,-$A$1)))
with A3 copied across
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dean" wrote in message
...
I have values in row 2, say in columns A through Z (so cells A2 thru Z2)
for values that happen in months 1 thru 26, respectively. In row 3, I
would like to see those same values appear, but delayed by "N" months.
Let's say N is the value of cell A1, a value that may change from time to
time. So, for example, if cell A2 has the value 4 in it, and if N=3, I
would like cell D3 to have the value 4 in it also.

I have seen this done with an offset function, but it doesn't seem to be
working for me. Can someone tell me what my equations in row 3 need to
be? Do I need to use a range name?

Thanks!
Dean

Well that helps a lot. I had someone else's file that worked fine and tried
to re-use their logic. Somehow, as the reference, they used a range name.
If I went to insert, then name, to look at the location of that range name,
it would always show it as being the cell that is in the same column as
whatever cell I was looking at - in other words, if I moved my cursor, the
cell of the range name changed. I found this odd, since I thought the range
name should show it as being the entire row. Obviously, there is some sort
of trick being used and by its finding the cell directly above, it is
finding the very cell that your approach directly specifies.

Can you explain how I could redo this using a range name as the reference,
just for my intellectual curiosity, even though your way is probably better
and simpler!

Thanks!
Dean


"Max" wrote in message
...
Perhaps you mean something like this in A3:
=IF($A$1="","",IF(ISERROR(OFFSET(A2,,-$A$1)),"",OFFSET(A2,,-$A$1)))
with A3 copied across
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dean" wrote in message
...
I have values in row 2, say in columns A through Z (so cells A2 thru Z2)
for values that happen in months 1 thru 26, respectively. In row 3, I
would like to see those same values appear, but delayed by "N" months.
Let's say N is the value of cell A1, a value that may change from time to
time. So, for example, if cell A2 has the value 4 in it, and if N=3, I
would like cell D3 to have the value 4 in it also.

I have seen this done with an offset function, but it doesn't seem to be
working for me. Can someone tell me what my equations in row 3 need to
be? Do I need to use a range name?

Thanks!
Dean

Try learning about Dynamic Name Ranging from
http://www.ozgrid.com/Excel/DynamicRanges.htm
This is a very underutilized concept that is worth the time to learn.

"Dean" wrote in message
...
Well that helps a lot. I had someone else's file that worked fine and
tried to re-use their logic. Somehow, as the reference, they used a range
name. If I went to insert, then name, to look at the location of that
range name, it would always show it as being the cell that is in the same
column as whatever cell I was looking at - in other words, if I moved my
cursor, the cell of the range name changed. I found this odd, since I
thought the range name should show it as being the entire row. Obviously,
there is some sort of trick being used and by its finding the cell
directly above, it is finding the very cell that your approach directly
specifies.

Can you explain how I could redo this using a range name as the reference,
just for my intellectual curiosity, even though your way is probably
better and simpler!

Thanks!
Dean


"Max" wrote in message
...
Perhaps you mean something like this in A3:
=IF($A$1="","",IF(ISERROR(OFFSET(A2,,-$A$1)),"",OFFSET(A2,,-$A$1)))
with A3 copied across
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dean" wrote in message
...
I have values in row 2, say in columns A through Z (so cells A2 thru Z2)
for values that happen in months 1 thru 26, respectively. In row 3, I
would like to see those same values appear, but delayed by "N" months.
Let's say N is the value of cell A1, a value that may change from time to
time. So, for example, if cell A2 has the value 4 in it, and if N=3, I
would like cell D3 to have the value 4 in it also.

I have seen this done with an offset function, but it doesn't seem to be
working for me. Can someone tell me what my equations in row 3 need to
be? Do I need to use a range name?

Thanks!
Dean

OK, one of these days I will. Frankly, until EXCEL figures out a way to
allow trace dependents to see through functions like offset, I am reticent
to use it very often. And functions like OFFSET seem to be why I should
learn this, though I imagine it is useful in conjunction with auditable
functions too.

Thanks!
Dean

"KC Rippstein" wrote in message
...
Try learning about Dynamic Name Ranging from
http://www.ozgrid.com/Excel/DynamicRanges.htm
This is a very underutilized concept that is worth the time to learn.

"Dean" wrote in message
...
Well that helps a lot. I had someone else's file that worked fine and
tried to re-use their logic. Somehow, as the reference, they used a
range name. If I went to insert, then name, to look at the location of
that range name, it would always show it as being the cell that is in the
same column as whatever cell I was looking at - in other words, if I
moved my cursor, the cell of the range name changed. I found this odd,
since I thought the range name should show it as being the entire row.
Obviously, there is some sort of trick being used and by its finding the
cell directly above, it is finding the very cell that your approach
directly specifies.

Can you explain how I could redo this using a range name as the
reference, just for my intellectual curiosity, even though your way is
probably better and simpler!

Thanks!
Dean


"Max" wrote in message
...
Perhaps you mean something like this in A3:
=IF($A$1="","",IF(ISERROR(OFFSET(A2,,-$A$1)),"",OFFSET(A2,,-$A$1)))
with A3 copied across
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dean" wrote in message
...
I have values in row 2, say in columns A through Z (so cells A2 thru
Z2) for values that happen in months 1 thru 26, respectively. In row 3,
I would like to see those same values appear, but delayed by "N" months.
Let's say N is the value of cell A1, a value that may change from time
to time. So, for example, if cell A2 has the value 4 in it, and if N=3,
I would like cell D3 to have the value 4 in it also.

I have seen this done with an offset function, but it doesn't seem to
be working for me. Can someone tell me what my equations in row 3 need
to be? Do I need to use a range name?

Thanks!
Dean