formula fill data validation from list without duplicated and blank

MKLAQ

Board Regular
Joined
Jan 30, 2021
Messages
103
Office Version
  1. 2016
Platform
  1. Windows
hello
I need dynamic formula when I add in the list a new items then shows automatically without I have to specify the range with considering ignore the duplicated items or blank cells
the data validation is in b2 and the list in column a
thanks
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
Something like this?

Note that it will not be possible without the extra column. Without office 365 dynamic arrays you will also need to ensure that the formula in column B is filled down far enough to return all entries. Beyond that, your only other option would be to use vba.

Second note, the formula in A4 is only there for the purpose of testing with a blank in the middle of the list. It is not required for the rest to work.
Book1
ABC
1ListSecond listValidation
2aab
3bb
4 d
5ae
6d 
7e 
8
Sheet3
Cell Formulas
RangeFormula
A4A4=""
B2:B7B2=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(list)/ISNA(MATCH(list,B$1:B1,0))/(list<>""),1)),"")
Named Ranges
NameRefers ToCells
list=OFFSET(Sheet3!$A$2,0,0,MATCH("zzz",Sheet3!$A:$A)-1,1)B2:B7
secondlist=OFFSET(Sheet3!$B$2,0,0,COUNTIF(Sheet3!$B:$B,">""")-1,1)B3:B7
Cells with Data Validation
CellAllowCriteria
C2List=secondlist
 

MKLAQ

Board Regular
Joined
Jan 30, 2021
Messages
103
Office Version
  1. 2016
Platform
  1. Windows
thanks I thought it's possible do that by formula I find this way is not practical as you said I need vba
I have to wait for somebody provides me the macro does that
thanks for your try I appreciate that :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,952
Messages
5,621,794
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top