Offset function only recognising rows

I have been using the offset function to create dynamic graphs. This has worked in to other virtually identical spreadsheet but in this worksheet the offset will only work for rows and columns (which is very frustrating as everything is laid out in column form). I've been staring at it for days now and think I need some outside perspective.

I have been defining the name using the formula (which has worked in my previous spreadsheets):

=OFFSET(LDContent!$B$4,0,0,-1,COUNTA(LDContent!$B:$N))

But it will not register as defining the column.

If however, I change the formula to either:

=OFFSET(LDContent!$B$4,0,0,COUNTA(LDContent!$B:$N),1)

or

=OFFSET(LDContent!$B$4,-1,0,COUNTA(LDContent!$B:$M))

It works perfectly fine for defining the rows.

Any one got any ideas why this could be happening?

Looking directly at the excel help page -

Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.

Use the Row/Column parameters to look up 1 cell in the range.

NBVT - In my tests it looks like any negative number is evaluated as 0.

Although the help files say you must have a positive value for height and width, OFFSET does work with negative values in those arguments (it just counts the height upwards - or width leftwards) so a formula like

=OFFSET(D3,0,0,-3)

gives you the range D1:D3

[ you can check by putting some values in D1:D3 and using =SUM(OFFSET(D3,0,0,-3)) ]

I think I read somewhere that Microsoft considers this to be a bug.....but if so I'm not sure why they don't fix it (it still works like that in 2010 beta).

Still, that doesn't answer the question here, I don't understand why you are using COUNTA across a multi-column range, which cells are populated in LDContent!$B:$N?

That's right using 1 or -1 basically makes no difference but try using a larger number (with enough room to have a negative "height"). If you use the formula I suggested in my last post, i.e.

=OFFSET(D3,0,0,-3)

this gives you a range 3 "high" but starting at D3 and going up....so it gives you the range D1:D3, whereas if you use 3 in place of -3 you still include D3 but the range goes the other way so you get D3:D5.

Technically, without using a negative value for the height you should use this version to get D1:D3

=OFFSET(D3,-2,0,3)

Gotcha, thanks for the clarification.