How to remove blank in dependent data validation dropdown list

[m_vishal_c]

HI
as you can see in the attached image, i need unique value for dropdown list from column A and in the second row i need the corresponding value (ignore blank) in another data validation drop down list

i.e if i select 1061 Surveyor from one dropdown list then another dropdown list will show "Surveyor", "Dream Drafting", "Atk surveying","Building professional..","Land surveying services", ïntrax consulting.."
but if i select 1011 Temprory fence then "Coates" shows blank too

i am using below formula but it shows with blank
R10 is Trade(Dropdown list)
i am applying below formula on next row to get Discipline / Trade name (but i need without blank)
=OFFSET('Supplier List'!A1,MATCH(R10,'Supplier List'!A:A,0)-1,1,COUNTIF('Supplier List'!A:A,R10))

Please guide me. i have wasted too much time but could not find .

Heaps thanks in advance

 

[m_vishal_c]

Ok, let's try something simple:
In workbook "m_vishal_c - 3 dependent data validation.xlsm"
1. Type something in E1 then hit Enter
2. Click Undo button (or Ctrl+Z), it will undo what you just typed.
3. Now type something again in E1, then select F2 (it has data validation)
4. Then Click Undo button (or Ctrl+Z) again, well, you can't, the Undo won't work, at this point you can't use Undo.

that mean i can not write in sheet if i have that coding . am I right ?. sorry i got your point, but its okay for me. i can delete that text instead of UNDO

 

[Akuini]

that mean i can not write in sheet if i have that coding . am I right

You can write anything in sheet, it just that the UNDO won't work each time you select a cell that has data validation.

i can delete that text instead of UNDO

Yes, of course

 

[m_vishal_c]

You can write anything in sheet, it just that the UNDO won't work each time you select a cell that has data validation.


Yes, of course

You dont know mate, you have removed so much stress. i applied same concept but by formula (Offset) but sheet keeps busy in calculation like (calculating 8 thread...%). so if i apply your code then sheet will be faster because of VBA code. isn't it ..I am very keen to see that code. thanks mate

 

[Akuini]

hi @Akuini
Before i say something, i must say your code is incredible.
there is a slightly change. you applied a range of F2:H2.
I need vertical like F2:- Trade Unique Dropdown, F3=Unique Supplier name dropdown depend on F2, F4=Unique Conctact Name dropdown depend on F3
and
I think these below will be changed
"
'range where data validation is located [in the example: "B2:D10"]
Private Const sDV As String = "F2:H6"
Private Const dc1 As Long = 6 ' first col, B is col 2
Private Const dc2 As Long = 8 ' last column, D is col 4 "

Please update, and let me know . Heaps thanks for your awesome support. Thanks

Sorry, I missed this post, let me see what I can do.

 

[m_vishal_c]

Sorry, I missed this post, let me see what I can do.

if you want i can send you entire file again

 

[Akuini]

OK, please, I need to know the layout.

 

[m_vishal_c]

thanks , please see below location

m_vishal_c_Dropdown list.xlsm

Shared with Dropbox

www.dropbox.com

 

[Akuini]

So where exactly do you want the data validation? In R10:R12? but it says in Q10:Q12 :

Trade
Company Name
Supplier Name

shouldn't it be:

Trade
Supplier name
Contact Name

 

[m_vishal_c]

So where exactly do you want the data validation? In R10:R12? but it says in Q10:Q12 :

Trade
Company Name
Supplier Name

shouldn't it be:

Trade
Supplier name
Contact Name

sorry for misleading you... i need dropdown list on R10(which will show dropdown of unique Trade), R11(which will show dropdown of unique Company Name), R12(which will show dropdown of unique Supplier Name. i have changed heading on file. please see updated file on below location

m_vishal_c_Dropdown list.xlsm

Shared with Dropbox

www.dropbox.com

thanks

 

[Akuini]

Ok, try this:

m_vishal_c_Dropdown list (1)

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Note:
You created table "Supplier_List_Table1" with the row far below until the last row of the sheet (i.e row 1048576) !!!. That really slows down the process. So I've deleted all row below the last row with data.