Offset Function

[jagbabbra]

Hi,

I am trying to use the offset function to pick out data from a seperate
worksheet. I use a variable in a cell that determines the location of
the relevent cells and found this works well.
My problem is that the offset function picks out the wrong data because
of some extra columns that I have, for instance......
If I decide that the relevent cells are 3 backwards from the offset
start point this works fine ....eg assuming the offset start point is
the 1 under april
Spreadsheet 1 Spreadsheet 2

Jan Feb Mar Apr ..... Reference 3 back gives
3 (under jan)
3 2 1 1

However by adding additional columns I do not pick up the information I
require... eg assuming the same case but in a different time
Spreadsheet 1

Nov Dec Year total BLANK COLUMN Jan Feb
3 2 15 2 3

Now assuming the start point of Feb (the 3) and going 3 cells back will
give the yearly total of 15 ... whereas I am actually after the 3 under
Nov.
I have been adjusting this so far by adjusting any offsets that cross
these blank columns and yearly totals but I would love to hear of a way
that can automate Excel so I do not need to trawl through the
calculations.

Any help would be much appreciated!!!
J

 

[Miguel Zapico]

You may use names with constants. For example, you can define the name
OffsetCols to 3 (menu Insert->Name->Define, type =3 in the Refers to box) and
use that value in your calculations. If you want to change the offset, just
go the same menu and change the value there, all your calculations will
reflect the new value.

Hope this helps,
Miguel.

 

[Domenic]

Are your dates true date values formatted as "mmm"? If not, first
change the dates so that the month and year are entered, and format as
desired. For example, enter the dates as follows...

November 2005
December 2005
January 2006
February 2006
etc.

Then, assuming that A10 contains the date of interest, such as February
2006, and B10 contains the offset, such as 3, try...

=INDEX(A2:F2,LARGE(IF(ISNUMBER(A1:F1),IF(A1:F1<=A10,COLUMN(A1:F1)-COLUMN(
A1)+1)),B10+1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!