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?

 

[azizrasul]

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

If you look at the new attached image file, depending on the name chosen, the number or=f rows differes. By including -1 as suggested, it works for my name but when I choose a different name, the number of rows may be less so I end up with blanks. I don't understand why COUNTA does not calculate correctly depending on the name chosen and the subsequent rows that it produces.

 

[Toadstool]

Hi Azizrusal,

I believe the answers you were given of subtracting one from your COUNTA would work but let me try another approach. You're already explicit about starting at $G$2 so why not pick a range starting at $G$2 for the COUNTA and ending at a row you'll never exceed. Maybe:

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

 

[RobVos]

That is what I posted previously -subtract 1.

 

[azizrasul]

That is what I posted previously -subtract 1.

Subtracting 1 or using

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

makes no difference. When I choose Jake Tempest, I still get extra blank rows as Jake has less rows than me i.e. Aziz. It doesn't behave in a dynamic way!£%^&?

 

[Toadstool]

You showed us the Named Range screen when Aziz was selected. Please show us that screen again when Jake Tempest is selected.

 

[azizrasul]

You showed us the Named Range screen when Aziz was selected. Please show us that screen again when Jake Tempest is selected.

 

[Toadstool]

There must be a character of some kind, probably a space, in the Report Name (or if those are derived entries then their source), so either make them null or try

=OFFSET(tblReports!$G$2,0,0,COUNTIF(tblReports!$G$2:$G$999,"> "))

 

[Fluff]

If you have a formula in col G then the cells are not empty & so Counta will count them.

 

[azizrasul]

If you have a formula in col G then the cells are not empty & so Counta will count them.

Ah, I didn't realise that "" would constitute data. Used the formula given post #17 and that works. Thanks all for staying with this, much appreciated.