How to remove blank in dependent data validation dropdown list

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    125 KB · Views: 28

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 7, 2016
Messages
179
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
OK, please, I need to know the layout.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 7, 2016
Messages
179
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


thanks
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
Ok, try this:

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

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,846
Members
416,138
Latest member
Pizzaman22

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