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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry my fault. I uploaded file without saving.
This is last file but I use two method at column G & H. Every want you want keep and another one you can delete.
For Column G, I only Do dropdown for Cell G2, For Other Cell at Column G take G2 and Drag it down.
Temp.xlsm
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
HI ALZ
thanks for letting me know but some data are not showing in drop down list. please see in below image
 

Attachments

  • Capture.JPG
    Capture.JPG
    103.1 KB · Views: 2

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
Sorry my fault. I uploaded file without saving.
This is last file but I use two method at column G & H. Every want you want keep and another one you can delete.
For Column G, I only Do dropdown for Cell G2, For Other Cell at Column G take G2 and Drag it down.
Temp.xlsm
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
Sorry my fault. I uploaded file without saving.
This is last file but I use two method at column G & H. Every want you want keep and another one you can delete.
For Column G, I only Do dropdown for Cell G2, For Other Cell at Column G take G2 and Drag it down.
Temp.xlsm
Hi Maabadi, its okay, i just tested that file but supplier name still shows duplicated on _1011TEMPRORYFENCE->Coates(which shows 3 times)
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

For Now , In Sheet 2 Go to _1011TEMPRORYFENCE Column and Select Two first Coates and at Home tab at Cell Section On Delete below array item Select Delete cells and then Select Shift Cells up.
I should Change macro and take times.
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
For Now , In Sheet 2 Go to _1011TEMPRORYFENCE Column and Select Two first Coates and at Home tab at Cell Section On Delete below array item Select Delete cells and then Select Shift Cells up.
I should Change macro and take times.
For Now , In Sheet 2 Go to _1011TEMPRORYFENCE Column and Select Two first Coates and at Home tab at Cell Section On Delete below array item Select Delete cells and then Select Shift Cells up.
I should Change macro and take times.
that's good but i dont think we should to manually to delete those duplication value in sheet2. if you can do in marco then it will be good. Please let me know once you complet by macrol heaps thanks
 

Akuini

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

ADVERTISEMENT

Hi, @m_vishal_c
This is an example of 3 dependent data validation, using VBA not formula.
The list in data validation is unique, sorted & has no blank.
But there's a drawback in using this method:
when a macro changes/writes something in the sheet it will clear the Undo Stack, so at that point you can't use UNDO.
In this case it happens whenever you put the cursor in cell with the data validation.

Let me know if you're interested in this method.

The example:

Applied on your sample:
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
hi @Akuini

heaps thanks for solving my 3 dependant drop down list. i checked file, it works very good. but very honest I don't understand your
"" But there's a drawback in using this method:
when a macro changes/writes something in the sheet it will clear the Undo Stack, so at that point you can't use UNDO.
In this case it happens whenever you put the cursor in cell with the data validation."

can you please explain in detail. thanks
 

Akuini

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

m_vishal_c

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

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

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