Obtaining blanks

[azizrasul]

Using

=OFFSET(tblReports!$G$2,0,0,COUNTA(tblReports!G:G))

I create a dynamic range of data called "Reports" in sheet "tblReports". However when I select a cell on another sheet called "Main", and do a Data Validation on a cell and select List and make the Source =Reports, I still get blanks appearring in the drop down! What can I be doing wrong?

 

[Toadstool]

Hi Azizrasul,

If you're referring to it in another sheet then make sure the scope is Workbook

 

[azizrasul]

That is how I have got it.

 

[azizrasul]

When I go into Name Manager and select Reports, it highlights the empty rows after the last populated cell. In column A in the same sheet the column is populated by 32 rows and Reports also highlights 32 rows of which most are empty.

I would not have thought that column A having more populated cells would make a difference?

 

[Toadstool]

I suspect you had a cell in column A selected when you went into Name Manager and it changed your relative address of G:G so try changing that to

=OFFSET(tblReports!$G$2,0,0,COUNTA(tblReports!$G:$G))

 

[azizrasul]

I have tried that and it kinda works except that I always get 1 blank line. Also the data changes based on a value I select on another data valaidation drop box. I have selected the last row and Cleared contents, but the I still get 1 blank line?

 

[RobVos]

Try:
=OFFSET(tblReports!$G$2,0,0,COUNTA(tblReports!$G:$G)-1)

 

[Toadstool]

I have tried that and it kinda works except that I always get 1 blank line. Also the data changes based on a value I select on another data valaidation drop box. I have selected the last row and Cleared contents, but the I still get 1 blank line?

You're using tblReports!$G:$G for the COUNTA but should start at $G$2 because of the heading. I don't understand the rest of what you're saying so try positing using XL2BB.

 

[azizrasul]

You're using tblReports!$G:$G for the COUNTA but should start at $G$2 because of the heading. I don't understand the rest of what you're saying so try positing using XL2BB.

Please see image file.

 

[Fluff]

You need to subtract 1 from the counta because you have a header in G1