OFFSET Function

  • Thread starter Thread starter Adam Smith
  • Start date Start date
A

Adam Smith

Dear Folks,

Using Excel 97, primarily,

Recently discovered the usefulness of the OFFSET function.

For example, I keep track of the Consumer Price Index, CPI, and currently
add each month's data at the top of a list, and then prepare a summary of
monthly changes, annual changes, bi-annual changes, etc.

OFFSET works nicely to deliver the last 12 data entry points, for instance.

But in setting up my OFFSET references, I wondered if there was a way to
"automate" the formula entry.

For instance, the top of my chart has the following formula,
=OFFSET(Data!E$2,12,0).

I manually changed the formula for the cell below to,
=OFFSET(Data!E$2,11,0)

Is there a way to express the formula so that I could copy the formula to
the appropriate cells, without having to manually edit the OFFSET formula?

thanks
 
One way

=OFFSET(Data!E$2,13-ROW(1:1),0)

will start at 12 and decrease accordingly to 11, 10 and so on
 
Thanks,

I'll give that a try.

Peo Sjoblom said:
One way

=OFFSET(Data!E$2,13-ROW(1:1),0)

will start at 12 and decrease accordingly to 11, 10 and so on

--

Regards,

Peo Sjoblom
Click to expand...
 
Back
Top