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

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try Use this:
Excel Formula:
=OFFSET('Supplier List'!A1,1,MATCH(R10,'Supplier List'!A:A,0)-1,COUNTA(OFFSET('Supplier List'!A1,1,MATCH(R10,'Supplier List'!A:A,0)-1,100,1)),1)
Replace 100 with Maximum number of rows for second dropdown at source file.

For more Details Check This:
Smart Dep. Drop-Down Lists in Excel: Expandable & Exclude Blank Cells
 
Last edited:

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Maybe you have problem with sheet name or don't update your cell references at formula with your data references.
Please upload your file with XL2BB Addin (Perferable) or upload at free uploading site, e.g. Dropbox and input link here.
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179

ADVERTISEMENT

Hi, Please see below link, i have uploaded file here
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I add macro to your file to Create Named Ranges at Sheet2.
Name of Main Named Range is "MainList"
For other Named Range because they cann't start with number, I add them "_" at first.
Then Select Range (Here Sheet1!E2:E118) for Trades go to Data validation select List, and at Source Box, Hit F3 and Select MainList
Then Select Range (Here Sheet1!F2) for Supplier Name go to Data validation select List, and at Source Box, and Write =indirect(E2) without $ and OK.
Check again that to Excel doesn't add to it $.
Then Drag E2 down to E118.
And at F2 write:
Excel Formula:
=IFERROR(VLOOKUP(F2,$B$2:$C$118,2,FALSE),"")
and drag it down.
Temp.xlsm
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179

ADVERTISEMENT

I must say a big "Thank you", just need to ask when i select _1011TEMPRORYFENCE (From E2), Unique supplier name shows 3 times "Coates", is it possible it shows unique name and column G shows different name in dropdown list
i.e E2=_1011TEMPRORYFENCE, F2="Coates"(unique), G2= all contact name in drop down list

heaps thanks
 

Watch MrExcel Video

Forum statistics

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