Validation lists needed to autopopulate, or not

[Monsignor]

The goal is for efficiency.
The user wants to have the options to:

1. Select component-by-component or,
2. Select a kit.

In 7 of 10 times, a Kit is going to be ordered.

My thought is to add a dropdown list in Column F that offers:
- Component
- Kit A
- Kit B
- Kit C


If "Component" is chosen, the user will manually select components in Columns G:J
If a Kit is chosen, Excel will automatically populate G:J based on the predetermined kit components.


At a higher level (I think I can handle this part) there needs to be a warning if someone selects a kit, then changes a component. No! That would be a Component order NOT a Kit order.


Note: I'm primarily a Formula guy. VBA is still something a bit foreign to me. I've used it but prefer worksheet functions. I would even prefer recording a macro before VBA. But I suspect this is going to call for at least a little VBA.

What are some thoughts on this puzzle?

 

[Jerry Sullivan]

Here is a non-VBA approach using Index-Match.

If you data worksheet is organized like this....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Component				Kit A				Kit B
2	Header 1	Header 2	Header 3	Header 4	Header 1	Header 2	Header 3	Header 4	Header 1
3	Part1	Brand1	Size1	Cost1	Part1A	Brand1A	Size1A	Cost1A	Part1B
4	Part2	Brand2	Size2	Cost2	Part2A	Brand2A	Size2A	Cost2A	Part2B
5	Part3	Brand3	Size3	Cost3	Part3A	Brand3A	Size3A	Cost3A	Part3B
6	Part4	Brand4	Size4	Cost4					Part4B
7	Part5	Brand5	Size5	Cost5					Part5B
8	Part6	Brand6	Size6	Cost6
Data

Then you can use Index-Match on your User-Interface sheet to look up the correct columns based on which Option (Component, Kit A, Kit B..) the user has selected from the Validation List drop down.

Excel Workbook
	C	D	E	F	G	H	I	J
1	Component		Select option:	Kit A	Part #	Brand	Size	Cost
2	Kit A				Part1A	Brand1A	Size1A	Cost1A
3	Kit B				Part2A	Brand2A	Size2A	Cost2A
4	Kit C				Part3A	Brand3A	Size3A	Cost3A
5
User Sheet

#VALUE!

</td></tr></table></td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#ffFCF9; color:#000000; "><tr><td ><b>Data Validation in Spreadsheet</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Allow</td><td >Datas</td><td >Input 1</td><td >Input 2</td></tr><tr><td >F1</td><td >List</td><td > </td><td >=vl_Options</td><td > </td></tr></table></td></tr><tr><td ><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#fffcf9; color:#000000; "><tr><td ><b>Names in Formulas </b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Name</td><td >Applies to</td></tr><tr><td >F1</td><td >vl_Options</td><td >='User Sheet'!$C$1:$C$4</td></tr></table></td></tr></table></td></tr></table>

 

[Monsignor]

JS411, thanks for the ideas. Your solution doesn't exactly fit. So, I'm including a snapshot of the problem.

In H11 and H13 there are Kits selected. When they're selected, I:M should auto-populate.
However, in H12 a component is selected. The user will manually have to select I:M.

Note: Everything in H:N is dropdown menus.

JS411, your solution has me thinking about using 2 rows. The upper row would have Kits, the lower row would have Component selections. That would definitely take up more space and I would need some validation around that solution to prevent users from entering data for both.

More ideas? Thanks for all the help!!

 

[Jerry Sullivan]

In H11 and H13 there are Kits selected. When they're selected, I:M should auto-populate.
However, in H12 a component is selected. The user will manually have to select I:M.

Oh....My interpretation of your layout was way off.

What you are describing calls for the cells in Col I:N to have two different personalities.

Unless you break the sheet into two sections, Components and Kits, I don't think it would be feasible to do that without VBA.

You could try the code below. It assumes you have a sheet that has the Kit data in Columns A:G.

It also assumes that Col I:N have Data Validation that allow choice of components.

To use the code (always test new code on a copy of your workbook):
1. Right Click on the Tab of your user-interface sheet
2. Select View Code
3. Paste the Code below into the code module
4. Edit the My Data sheet name to match your data sheet name
5. Close the editor

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Intersect(Target, Columns("H:N")) Is Nothing) Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
 
    If Not (Intersect(Target, Columns("H:H")) Is Nothing) Then
        If Target.Value = "Component" Then 'Clear row and use dropdowns
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 3).Resize(1, 4).ClearContents
        Else 'Use Vlookups
            Target.Offset(0, 1).FormulaR1C1 = _
                "=VLOOKUP(RC8,'My Data'!C1:C7,COLUMN(RC[-7]),FALSE)"
            Target.Offset(0, 3).Resize(1, 4).FormulaR1C1 = _
                "=VLOOKUP(RC8,'My Data'!C1:C7,COLUMN(RC[-7]),FALSE)"
        End If
    Else 'Target in Columns I:N
        If Cells(Target.Row, 8).Value <> "Component" Then
            Target.FormulaR1C1 = _
                "=VLOOKUP(RC8,'My Data'!C1:C7,COLUMN(RC[-7]),FALSE)"
            MsgBox "Can't edit parts of a kit!", vbExclamation, "Error"
        End If
    End If
 
CleanUp:
    Application.EnableEvents = True
End Sub

Hope this helps!

 

[Monsignor]

Jerry!! WOW! Ok.

First, I hadn't forgotten about this. Thanks for all the work.

Second, it looks like this is going to work. I have to tweak the code and my spreadsheet a bit but this is looking like it's going to work.

I will let you know! Thanks brother!!

 

[Monsignor]

Lots of this is working nicely. Two problems:

1. If I select 1x4 Strip Kit, the components show up. Lt's say I change my mind, go back to the dropdown and select [blank] to zero out the cell, columns I:N go to #N/A when they should all go blank.

In cases like these, I would use, =IF(H11="","", VLOOKUP( ... ))

2. Again, I select 1x4 Strip kit, then change my mind. "Oops! I meant to select 'Components.'" I start to select components and the Msg box comes back "Can't edit parts of a kit."

How can we adjust the code for this?

 

[Jerry Sullivan]

1. If I select 1x4 Strip Kit, the components show up. Lt's say I change my mind, go back to the dropdown and select [blank] to zero out the cell, columns I:N go to #N/A when they should all go blank.

In cases like these, I would use, =IF(H11="","", VLOOKUP( ... ))

That would work; however if H11 is blank, there is no reason to put formulas in I:N. Instead those can be blanked out just like they are for H11="Component"

Modify the code like this....

If Not (Intersect(Target, Columns("H:H")) Is Nothing) Then
    If Target.Value = "Component" Or _
           Target.Value = vbNullString Then 'Clear row and use dropdowns
        Target.Offset(0, 1).ClearContents

2. Again, I select 1x4 Strip kit, then change my mind. "Oops! I meant to select 'Components.'" I start to select components and the Msg box comes back "Can't edit parts of a kit."

How can we adjust the code for this?

That sequence shouldn't cause that result if you have the same Columns as my example. The "Can't edit parts of a kit." message only occurs in response to a change to Columns I:N (when H is not equal to "Component". You should be able to change Column H repeatedly without triggering that message. Perhaps in adjusting the code for your layout your columns are aligned slightly differently?

 

[Jerry Sullivan]

I just re-read your post and I think I misinterpreted what you were describing for question #2. I thought you were describing that the user picks the option "Component" from the dropdown list in H and gets an error message. Now I realize you are asking, what if they start picking components from Col I:N?

One option is to change the error message and direct the user to explicitly pick "Components" instead of having Col H blank.
Based on your question, it sounds like your preference would be to allow them to start picking components. (You're building-in a lot of features for someone who didn't want to have any VBA initially!)

In that case maybe as soon as they make that first pick in Col I:N, then the Cell on H gets set to "Component".

Here is the revised code....

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Intersect(Target, Columns("H:N")) Is Nothing) Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
 
    If Not (Intersect(Target, Columns("H:H")) Is Nothing) Then
        If Target.Value = "Component" Or _
                Target.Value = vbNullString Then 'Clear row and use dropdowns
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 3).Resize(1, 4).ClearContents
        Else 'Use Vlookups
            Target.Offset(0, 1).FormulaR1C1 = _
                "=VLOOKUP(RC8,'My Data'!C1:C7,COLUMN(RC[-7]),FALSE)"
            Target.Offset(0, 3).Resize(1, 4).FormulaR1C1 = _
                "=VLOOKUP(RC8,'My Data'!C1:C7,COLUMN(RC[-7]),FALSE)"
        End If
    Else 'Target in Columns I:N
        If Cells(Target.Row, 8).Value = vbNullString Then
            Cells(Target.Row, 8).Value = "Component"
        Else
            If Cells(Target.Row, 8).Value <> "Component" Then
                Target.FormulaR1C1 = _
                    "=VLOOKUP(RC8,'My Data'!C1:C7,COLUMN(RC[-7]),FALSE)"
                MsgBox "Can't edit parts of a kit!", vbExclamation, "Error"
            End If
        End If
    End If 
CleanUp:
    Application.EnableEvents = True
End Sub

 

[Monsignor]

(You're building-in a lot of features for someone who didn't want to have any VBA initially!)

My preference would have been for no VBA but I saw that it wasn't going to be possible. Ahhh ... but I'm learning and appreciate your work with me.

Ok. I'm reviewing the latest code ...

 

[Monsignor]

Here's what's working:

- If I choose a kit, the components populate
- If I choose a kit then go back and choose BLANK, the dependent cells do empty
- If I choose a kit, the components show up. If I try to change a component, the warning comes up.
- If H11 is blank, and I go to choose a component, H11 autopopulates to COMPONENTS.
- If I select a component and H11 is blank, it autopopulates to COMPONENTS

What's not working:
- There are some tweaks that I have to make, and rearrange some data on the worksheet where my source data is.
Conceptually it's working perfectly!

After I've rearranged the data and done more testing, I'll be back to let you know how it went. Thanks so much!