Dynamic Range to not include Blank Values (even if cell has formula)

[justanotheruser]

Hi guys,

I've got this table below whereby row C contains data inputted by a user and there could be duplicate entries, so I've got formulas in Column A and B that remove the duplicate entries, as you can see:

Excel Workbook
	A	B	C	D
1			Entity	Additional Sheets to open
2
3	1	FA6_210	FA6_210	PL_Workwear
4	1	FA7_220	FA6_210	BS_Workwear
5	1	FA8S_220	FA6_210	PL_Cleanroom
6	2	FA89_230	FA7_220	PL_Workwear
7	3	KK_230	FA8S_220	BS_Workwear
8	4	210	FA89_230	PL_Cleanroom
9	5		KK_230	PL_Workwear
10	5		KK_230	BS_Workwear
11	6		210
12	7
13	8
Mapping

The problem I have is that I am trying to use the contents of Column B in a dynamic drop down list, where there should be no blanks:


If we look at Column B, where I have defined a name, called ENTA with the following: =OFFSET(Mapping!$B$3,0,0,COUNTA(Mapping!$B:$B),1) - in cell B10, even though the cell value is blank, because there is a formula in it, the cell is chosen as part of the dynamic range - is there a way to adjust the offset formula to fix this? The cells in Column B are formatted as [=0]"";General

Thanks!

 

[justanotheruser]

I've been googling and I can only see examples where there is no data whatsoever in a cell - i.e. no formula either will Excel recognise it as blank. Alternatively, if I remove the formatting of the cells in column B, the blank will show up as a 0 value - would something magical be able to be done with that? Thanks again!

 

[AlphaFrog]

This array formula in B3 (and copied down to B13) will return unique entries from column C. No need for column A in this case.

B3

=IF(ISNA(INDEX($C$3:$C$13, MATCH(0, COUNTIF($B$2:B2, $C$3:$C$13), 0))), "", INDEX($C$3:$C$13, MATCH(0, COUNTIF($B$2:B2, $C$3:$C$13), 0)))

The last result in the column B unique list will be 0
This ENTA Dynamic Named Range formula will look for that zero to determine the count of unique entries and not include the zero or blanks cells at the bottom of the list.

ENTA

=OFFSET(Mapping!$B$3,0,0,MATCH(0,Mapping!$B$3:$B$13,0)-1)

You could shorten the B3 formula to this if you don't care if the #N/A error is displayed in cells in column B. It would not affect the ENTA formula

=INDEX($C$3:$C$13, MATCH(0, COUNTIF($B$2:B2, $C$3:$C$13), 0))

 

[justanotheruser]

Thanks for your response. My sheet now looks like this:

Excel Workbook
	A	B	C	D
1			Entity	Additional Sheets to open
2
3		Select Entity	Select Entity
4		FA6_210	FA6_210	BS_Workwear
5		FA7_220	FA6_210	PL_Cleanroom
6		FA8S_220	FA7_220	PL_Workwear
7		FA89_230	FA8S_220	BS_Workwear
8		KK_230	FA89_230	PL_Cleanroom
9		210	KK_230	PL_Workwear
10		230	KK_230	BS_Workwear
11		0	210
12			230
13
Mapping

And in Name Manage, Name is ENTA, and Refers to is =OFFSET(Mapping!$B$3,0,0,MATCH(0,Mapping!IT1:IT11,0)-1)

However, if I type "ENTA" without the "" in the name box to show the range, nothing happens. Also, is it possible to make the code dynamic for the countif $C$3:$C$13 or just extent it significantly as there will probably be around 100 rows for the column C.

Thanks again!

 

[AlphaFrog]

This should be the ENTA formula

=OFFSET(Mapping!$B$3,0,0,MATCH(0,Mapping!$B$3:$B$13,0)-1)

This should be the Data Validation formula
=ENTA

You can make C3:C13 (And $B3:$B$13 in the ENTA formula) to include more rows than you need e.g. C3:C500

 

[justanotheruser]

You sir, are a genius! Thank you very much!!