2

I have a series of data sets (later to be used for populating comboboxes), and I've tried setting up dynamic ranges to list only the cells with useful data. In total, there are 160 rows of data, but the number of rows that will be populated will vary wildly.

In case it has a bearing on it (if the dynamic ranges detect "" as not blank, for example), the formula used to populate the cells in the range is

{=IF(ROW()-ROW(StartPort_NoBlanks)+1>ROWS(StartPort_Blanks)-COUNTBLANK(StartPort_Blanks),"",INDIRECT(ADDRESS(SMALL((IF(StartPort_Blanks<>"",ROW(StartPort_Blanks),ROW()+ROWS(StartPort_Blanks))),ROW()-ROW(StartPort_NoBlanks)+1),COLUMN(StartPort_Blanks),4)))}

(Based on @DennisWilliamson's answer at https://superuser.com/questions/189737/dynamically-updating-list-of-unique-column-entries-in-excel )

So far, I've tried both

='Saves_FilterLookups'!$C$3:INDEX('Saves_FilterLookups'!$C$3:$C$162, COUNTA('Saves_FilterLookups'!$C$3:$C$162))

and

=OFFSET('Saves_FilterLookups'!$C$3,0,0,COUNTA('Saves_FilterLookups'!$C:$C),1)

...but both give me the whole list, including the "blank" cells.

Most of the ranges contain text strings; one of the other ranges contains dates.

What am I missing? How do I fix it?

EDIT: To give a bit of context, here's a bit of the list. The full list contains entries scattered along its length, duplicates are removed into the second column, and they're all consolidated into a single block in the third column. They're then populated into the combobox in the userform, but all the blanks are coming in too...

Source to combobox

Community
  • 1
  • 1
Andrew Perry
  • 743
  • 2
  • 11
  • 32
  • 1
    I believe it will be useful if you give an exemplary input and output. It's easy to misinterpret descriptively given objectives. – zaptask Jun 08 '16 at 11:34
  • Done... I think. Hopefully it'll clarify it a bit. – Andrew Perry Jun 08 '16 at 11:45
  • Not spaces, no; all the blank cells are `""` blanks with formulae, but there aren't any spaces there. I'll give the LEN check a try and get back to you. – Andrew Perry Jun 08 '16 at 12:59
  • Its not a space issue. its a counting issue. COUNTA and COUNTIF. I tried doing a COUNTIF(range,"<>"&"") but that did not work despite the demo saying it would. – Forward Ed Jun 08 '16 at 13:04
  • Your comment I was replying to seems to have disappeared. LEN returns 0 for the blank cells as it should. – Andrew Perry Jun 08 '16 at 13:16

1 Answers1

1

Here is a potential solution for you...the caveat is the cell above the list needs to be blank

=IFERROR(INDEX($A$2:$A$13,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$13),0,0),0)),"")

That will generate the list all in one step sorted and blanks removed.

I believe your COUNTA is your culprit. COUNTA will count all cells with something in them...and unfortunately "" is something. So you will need to eliminate the count of "". so I added a -COUNTIF and assumed the same range your were counting before. The worksheet name is not required if it is all being done on the same worksheet.

=OFFSET('Saves_FilterLookups'!$C$3,0,0,COUNTA('Saves_FilterLookups'!$C$3:$C$162)-COUNTIF('Saves_FilterLookups'!$C$3:$C$162,""),1)
Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • Yeah, the worksheet name is there for the range definition. I'll try your suggestion and get back to you. – Andrew Perry Jun 08 '16 at 13:18
  • Working from your suggestion, I changed the `$C:$C` to a limited range and adjusted for the overlap. `=OFFSET(Saves_FilterLookups!$C$3,0,0,COUNTA(Saves_FilterLookups!$C$3:$C$162,1)-COUNTIF(Saves_FilterLookups!$C$3:$C$162,"")-1)` works nicely. If you'd like to edit that into your answer I'll get it ticked. Thanks! – Andrew Perry Jun 08 '16 at 13:30
  • I edited the formula and noticed a mistake in it at the same time. your COUNTA(C3:C162,1) has a ,1 that really should not be there. it may be throwing the count off. and the last -1 maybe should not be there as a result...and potentially you meant to type ,1 instead not sure. Try out the edited formula and see if it works. – Forward Ed Jun 08 '16 at 13:42
  • Aaaaah, that explains why it got that extra 1 that I had to put the -1 in to fix. The hazards of copy-pasting code and assuming the tutorial got it right. :-D – Andrew Perry Jun 08 '16 at 13:51
  • `=OFFSET(Saves_FilterLookups!$C$3,0,0,COUNTA(Saves_FilterLookups!$C$3:$C$162)-‌​COUNTIF(Saves_FilterLookups!$C$3:$C$162,""))` – Andrew Perry Jun 08 '16 at 13:51
  • I left the ,1 in there because the format of the offset is `OFFSET(REFERENCE, ROWS SHIFT, COLUMN SHIFT, # ROWS to RETURN, # COLUMNS to RETURN)` Now the last two are optional, but I like to put them in which is exactly what that last ,1 is doing. Its saying return 1 column wide. – Forward Ed Jun 08 '16 at 13:57
  • ...and now neither of the blasted things works. Oh, dynamic ranges, why do ye vex me so? – Andrew Perry Jun 08 '16 at 14:03
  • Right, I'll try that. Cheers. – Andrew Perry Jun 08 '16 at 14:03
  • Found the problem. Some control characters had got in by the minus sign (`-` instead of `-`), which broke the formula. Got rid of them and now it works. Copy and paste really having it in for me today, it seems. Thanks for your patience. – Andrew Perry Jun 08 '16 at 14:12
  • 1
    OK, dog is happy to hear that as she is starring at me wanting to go for her morning walk and I have been saying NO, need to answer question! Off for a WALK now though. Enjoy your morning 8) – Forward Ed Jun 08 '16 at 14:16
  • My evening - just about to go home from work. Have a good walk, and thanks again. :-) – Andrew Perry Jun 08 '16 at 14:20