Offset function

[Dean]

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

 

[Max]

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

 

[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

 

[KC Rippstein]

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]

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