Data Validation to not show blank / empty cell

[Ateeb Ali]

Dear Sir
I want to apply data validation on cell : Sheet2!B5
Data Validation List: Sheet1!U3:U15

I want validation to show only those cells which have entries like if there are values in cell U3 & U4, it should show two values only

 

[rahulshewale1]

hii @ateeb,


Could you please provide sample file with desire data


Rahul shewale

 

[Ateeb Ali]

Dear Sir, it is attached

 

[rahulshewale1]

Hii @Ateeb Ali ,

pfa sheet.
helper column - Cell F5


=IFERROR(SINGLE(INDEX(Sheet1!$T$3:$T$46,AGGREGATE(15,6,(ROW(Sheet1!$T$3:$T$46)-ROW(Sheet1!$T$3)+1)/(Sheet1!$T$3:$T$46<>""),ROWS(Sheet2!$F$4:F4)))),)


Name manger - formula

=Sheet2!$F$5:INDEX(Sheet2!$F$5:$F$20,COUNTIFS(Sheet2!$F$5:$F$20,"*?*"))



Regard
rahul shewale

 

[rahulshewale1]

hii

attached sheet.

 

[Ateeb Ali]

Sir, its not working

 

[rahulshewale1]

hi @Ateeb Ali

i did not understand your requirement.


Please provide sample desire data.

 

[Ateeb Ali]

Please see attached sample file

 

[Ateeb Ali]

Appreciate if you respond back

 

[bosco_yip]

Try................

1] In "Sheet1 - Index no" R3, copied down :

=IF(S3=FALSE,"",MAX(R$2:R2)+1)

2] In "Sheet1 - Selected" T3, copied down :

=IFERROR(INDEX($B$3:$B$46,MATCH(ROWS(A$1:A1),$R$3:$R$46,0)),"")

3] Delete all formulas in "Sheet1" Column U

4] Define Name >> Name : Stitch
>> Refer to: =OFFSET(Sheet1!$T$3,0,0,COUNTIF(Sheet1!$T:$T,"?*")-1,1)
>> OK

5] In "Sheet 2" B5 >> Data Validation >> Setting
>> Allow : List
>> Sources : =Stitch
>>OK

Regards
Bosco

 

[Ateeb Ali]

Sir, this works perfect, thanks a lot

 

[Ateeb Ali]

Dear Sir
Need your help in this file as well, I want data validation not to show blank cells
Cell I6: is where validation is showing
C5:C32 is data range from where validation is picking values

I have tried all via name manager and tried all formulas but its not happening, may be I am doing some mistake

 

[vletm]

Ateeb Ali
You haven't tried this ...

 

[Ateeb Ali]

Thanks Sir, you are great