动态范围再次 - 与文本字符串 [英] Dynamic ranges again - once more, with text strings

问题描述

我有一系列数据集（稍后用于填充组合框），我已经尝试设置动态范围，仅列出具有有用数据的单元格。总共有160行数据，但是要填充的行数会有很大的变化。

如果它对它有影响（如果动态范围检测不为空，例如）用于填充范围内的单元格的公式是

  {= 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））}} 
  

（基于@ Dennis威廉姆森的答案

解决方案

这是一个潜在的解决方案，请注意，列表上方的单元格必须为空白

  = IFERROR（INDEX（$ A $ 2：$ A $ 13，MATCH（0，INDEX（COUNTIF（$ B $ 1：B1，$ A $ 2 ：$ A $ 13），0,0），0）），）
  

该列表全部按顺序排列，并将空白移除。

我相信您的COUNTA是您的罪魁祸首。 COUNTA会对所有的单元格进行计数，而不幸的是是的。所以你需要消除的计数。所以我添加了一个-COUNTIF，并假设你以前计数的范围相同。如果全部在同一工作表上完成，则不需要工作表名称。

  = OFFSET（'Saved_FilterLookups'！$ C $ 3,0,0，COUNTA（'Saved_FilterLookups'！$ C $ 3：$ C $ 162）-COUNTIF（'Saved_FilterLookups'！$ C $ 3：$ C $ 162，），1）
  

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...

解决方案

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)

这篇关于动态范围再次 - 与文本字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！