Why isn't this OFFSET function working correctly !

My OFFSET function expands down 1 row to many, can ayone tell me why?
This is what I'm using for my pivot table so I always end up getting a "blank Field" in my pivot table.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Col A

Psword Teacher Month Infractions
2350538 Obama h)Feb 1
2350538 Obama i)Mar 1
2350538 Obama j)Apr 7

So when the above offset function is applied, it picks up the above 4 fields PLUS it adds a blank row to the bottom so it is actually picking up 5 fields...
Can anyone HELP !!!!

It's counting all cells which have something entered in them. Is there another cell in Column A that has something in it (e.g. a title or something)? If not, select all the cells in column A excluding those 4 and "clear contents". Did that help?

why stop at A1000 your OFFSET formula is checking every cell in column A

Also how does you formula return 4 fields when the number of columns is set to 1?

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Perhaps you should post a workbook.

The counta counts the number of rows with data. So assuming A2000 has content the result would be 5. So a 5 row offset from A1 would be the range A1:A5. And because A5 is blank that's where you blank item comes from.

You can adjust the formula to just work with the 1st 1000 rows.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),4)

A3 has a value in it. Either minus 1 from your COUNTA argument or start counting from A6

Please Login or Register  to view this content.

Working now?