Getting #ref error after row / cell is this a limit in excel 2003 tiasal2
 

Getting #ref error after row / cell is this a limit in excel 2003 tia
sal2
Greetings All

I’m getting a strange #ref error message in excel and I’m not sure
why. Everything works great up to cell and row……But when I get past
that the #ref error pops up. Is this a limit to excel 2003? Anyone
have any workarounds or recommendations?

The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS
($1:31)+COLUMNS($D:D)-2)

An image can be found at http://test.onewithall.net/problem/excel_problem.jpg
or the Excel file can be found at is http://test.onewithall.net/problem/excel_problem.xls

Tia sal2

Getting #ref error after row / cell is this a limit in excel 2003 tia sal2
 

The problem is that you have a total range size of 60 cells indexed:

=INDEX($D$3:$D$62 = 60

But you're asking the formula to return cell number 61:

2*ROWS($1:31)+COLUMNS($D:D)-2 = 61

Since there is no cell number 61 in the indexed range you get the #REF!
error.

--
Biff
Microsoft Excel MVP


wrote in message
...
Getting #ref error after row / cell is this a limit in excel 2003 tia
sal2
Greetings All

I’m getting a strange #ref error message in excel and I’m not sure
why. Everything works great up to cell and row……But when I get past
that the #ref error pops up. Is this a limit to excel 2003? Anyone
have any workarounds or recommendations?

The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS
($1:31)+COLUMNS($D:D)-2)

An image can be found at
http://test.onewithall.net/problem/excel_problem.jpg
or the Excel file can be found at is
http://test.onewithall.net/problem/excel_problem.xls

Tia sal2

Getting #ref error after row / cell is this a limit in excel 2003tia sal2
 

You are trying to index the range D3:D62 (i.e. 60 cells), but your
expression:

2*ROWS($1:31)+COLUMNS($D:D)-2

will evaluate to 61, so Excel is telling you that this is outside the
range you are trying to index.

Hope this helps.

Pete

On Mar 20, 4:35*pm, wrote:
Getting #ref error after row / cell is this a limit in excel 2003 tia
sal2
Greetings All

I’m getting a strange #ref error message in excel and I’m not sure
why. *Everything works great up to cell and row……But when I get past
that the #ref error pops up. *Is this a limit to excel 2003? Anyone
have any workarounds or recommendations?

The code I start to get the error at is =INDEX($D$3:$D$62,2*ROWS
($1:31)+COLUMNS($D:D)-2)

An image can be found athttp://test.onewithall.net/problem/excel_problem.jpg
or the Excel file can be found at ishttp://test.onewithall.net/problem/excel_problem.xls

Tia sal2