Auto-populate cell if data validation list has only one option

[jruddat]

Hi all,
I have a series of drop-down lists created via data validation which pull from a separate sheet of dynamic lists that are driven by a series of rules. In other words, my sheet consists of a series of conditional drop-down lists, each one affecting the available options in the lists that come after it.

In order to best explain my issue I am going to describe two scenarios.
Lets say the first drop-down list allows the user to select from a series of hardware types (cabinet knobs, drawer pulls, robe hooks, etc.). The next drop-down lists allows the user to select from a list of hardware mounting options (through-bolted, surface-mounted, or back-to-back).

Scenario 1:
The user selects drawer pulls from the first drop-down list. The second drop-down allows the user to select either through-bolted, surface-mounted, or back-to-back as an option.

Scenario 2:
The user selects cabinet knobs for the first drop-down list. In this case the second drop-down list only allows them to select through-bolted as an option since the hardware company only sells through-bolted cabinet knobs.

My dilemma is this: I want to retain the ability for the user to select either through-bolted, surface-mounted, or back-to-back in the first scenario, but at the same time I want this same cell to be automatically populated with "through-bolted" given the second scenario so that the user does not have to go through the action of selecting "through-bolted" from a list that has only one option anyway. There are many scenarios where a proceeding drop-down list will end up having only one option based on what was selected for preceding drop-down lists and I want to make the number of button clicks necessary for the user as low as possible.

Any ideas or is this even possible?

Thanks,
Jack

 

[Jeffrey Mahoney]

Describe your layout. Do you have these pulldowns in many rows or just one location. A Macro would have to run after Hardware Type pulldown has changed. The macro would check for the number of items in the Mounting Style and change that cell appropriately. I would need cell addresses or named ranges.

 

[jruddat]

Describe your layout. Do you have these pulldowns in many rows or just one location. A Macro would have to run after Hardware Type pulldown has changed. The macro would check for the number of items in the Mounting Style and change that cell appropriately. I would need cell addresses or named ranges.

Each pull-down is in it's own row. All pulls-downs exist under one column.
Each of these pull-downs take their data from dynamic lists in another sheet. If I am understanding you correctly, for a given pull-down, all I would need is a macro to check if the list it pulls from has just one value? In such a case, the macro would enter data into that cell, correct?

 

[Jeffrey Mahoney]

I realize this is going to be more than what you asked for and a little complicated. It works real well

On Shee1 below, there is a dynamic filter for Mounting Types. Choose a Hardware Type in Column C and the filter in Column will provide only the Mounting types for that. The macro below will detect a change in column C and set the Mounting Type value in Column D if there is only one Mounting Type list for that Hardware. Column C uses HardwareFilter in the data validation. Column D uses =G2# in the data validation. The Hardware Table must have one row per Hardware Type/Mounting Type combo.

Let me know if you have questions

Sheet 1 has the data validation and a mounting filter per row

Book45a 20240313.xlsm
	C	D	E	F	G	H	I
1	HardwareCol	MountingCol			MountingFltrCol
2	Cabinet Knob				Through-bolted	Surface-mounted
3	Closet Hook	Back-to-Back			Back-to-Back
4	Drawer Pull	Through-bolted			Through-bolted
5	Robe Hook	Surface-mounted			Surface-mounted
Sheet1

Cell Formulas
Range	Formula
G2:H2,G3:G5	G2	=TRANSPOSE(FILTER(MountingStyle,HardwareType=Sheet1!C2,""))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
HardwareCol	=Sheet1!$C$2:$C$74	G2
HardwareTbl	=Sheet2!$L$3:$M$7	G2:G5
HardwareType	=Sheet2!$L$3:$L$7	G2:G5
MountingStyle	=Sheet2!$M$3:$M$7	G2:G5

Cells with Data Validation
Cell	Allow	Criteria
C2:C22	List	=HardwareFilter#
D2:D26	List	=G2#

Sheet2 has the Hardware Types, Mounting Styles for each and a filter of unique Hardware Types

Book45a 20240313.xlsm
	J	K	L	M
2	Hardware Filter		Hardware Type	Mounting Style
3	Cabinet Knob		Cabinet Knob	Through-bolted
4	Closet Hook		Cabinet Knob	Surface-mounted
5	Drawer Pull		Drawer Pull	Through-bolted
6	Robe Hook		Robe Hook	Surface-mounted
7			Closet Hook	Back-to-Back
Sheet2

Cell Formulas
Range	Formula
J3:J6	J3	=SORT(UNIQUE(HardwareType))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
HardwareTbl	=Sheet2!$L$3:$M$7	J3
HardwareType	=Sheet2!$L$3:$L$7	J3

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim FltrCel As Range
  
  Set i = Intersect(Range("HardwareCol"), Target)
  If Not i Is Nothing Then
    If i.Count > 1 Then Exit Sub
    If i.Value = "" Then Exit Sub
    Set FltrCel = Intersect(i.EntireRow, Range("MountingFltrCol"))
    If Application.CountA(Range(FltrCel, FltrCel.Offset(0, 100))) = 1 Then
      Application.EnableEvents = False
      Intersect(i.EntireRow, Range("MountingCol")).Value = FltrCel.Value
      Application.EnableEvents = True
    End If
  End If
  
    
End Sub

 

[Jeffrey Mahoney]

On Sheet1 column G (Mounting FltrCol) can be pushed over to the right or the columns could be hidden

 

[Jeffrey Mahoney]

The Xl2bb tool didn't get the named ranges for the MountingCol and MountingFltrCol which are D2:D47 and G2:G47 respectively.

 

[jruddat]

I realize this is going to be more than what you asked for and a little complicated. It works real well

On Shee1 below, there is a dynamic filter for Mounting Types. Choose a Hardware Type in Column C and the filter in Column will provide only the Mounting types for that. The macro below will detect a change in column C and set the Mounting Type value in Column D if there is only one Mounting Type list for that Hardware. Column C uses HardwareFilter in the data validation. Column D uses =G2# in the data validation. The Hardware Table must have one row per Hardware Type/Mounting Type combo.

Let me know if you have questions

Sheet 1 has the data validation and a mounting filter per row

Book45a 20240313.xlsm
	C	D	E	F	G	H	I
1	HardwareCol	MountingCol			MountingFltrCol
2	Cabinet Knob				Through-bolted	Surface-mounted
3	Closet Hook	Back-to-Back			Back-to-Back
4	Drawer Pull	Through-bolted			Through-bolted
5	Robe Hook	Surface-mounted			Surface-mounted
Sheet1

Cell Formulas
Range	Formula
G2:H2,G3:G5	G2	=TRANSPOSE(FILTER(MountingStyle,HardwareType=Sheet1!C2,""))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
HardwareCol	=Sheet1!$C$2:$C$74	G2
HardwareTbl	=Sheet2!$L$3:$M$7	G2:G5
HardwareType	=Sheet2!$L$3:$L$7	G2:G5
MountingStyle	=Sheet2!$M$3:$M$7	G2:G5

Cells with Data Validation
Cell	Allow	Criteria
C2:C22	List	=HardwareFilter#
D2:D26	List	=G2#

Sheet2 has the Hardware Types, Mounting Styles for each and a filter of unique Hardware Types

Book45a 20240313.xlsm
	J	K	L	M
2	Hardware Filter		Hardware Type	Mounting Style
3	Cabinet Knob		Cabinet Knob	Through-bolted
4	Closet Hook		Cabinet Knob	Surface-mounted
5	Drawer Pull		Drawer Pull	Through-bolted
6	Robe Hook		Robe Hook	Surface-mounted
7			Closet Hook	Back-to-Back
Sheet2

Cell Formulas
Range	Formula
J3:J6	J3	=SORT(UNIQUE(HardwareType))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
HardwareTbl	=Sheet2!$L$3:$M$7	J3
HardwareType	=Sheet2!$L$3:$L$7	J3

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim FltrCel As Range
 
  Set i = Intersect(Range("HardwareCol"), Target)
  If Not i Is Nothing Then
    If i.Count > 1 Then Exit Sub
    If i.Value = "" Then Exit Sub
    Set FltrCel = Intersect(i.EntireRow, Range("MountingFltrCol"))
    If Application.CountA(Range(FltrCel, FltrCel.Offset(0, 100))) = 1 Then
      Application.EnableEvents = False
      Intersect(i.EntireRow, Range("MountingCol")).Value = FltrCel.Value
      Application.EnableEvents = True
    End If
  End If
 
   
End Sub

This is great, thank you for the help Jeffrey!

 

[Jeffrey Mahoney]

You are welcome. Remember the VBA code needs to be put in a SHEET module under the sheet name that has the choices for Hardware and Mounting