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
I am very much impressed with your code. it works perfectly. Big thanks. I am going to close this query. if i need something in future, how can i contact you. thanks
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.
You can mention me in any thread (@Akuini).
 
  • Like
Reactions: alz

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,822
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