Topic: Are there any MS Excel professionals here?

[jualdeaux]

I am tying to make a special spreadsheet and I have no idea how to do something. It may not even be possible.

 I am trying to make a sheet that has a list where each item has two combo boxes. The first one has two items in it. The second one willbe conditional on what is choosen in the first box. So, an example might be where the first box has "pistol" and "rifle" as the options. If I choose pistol, the second box will have a listing of all the different pistol calibres in it. If I choose rifle, it will have all the rifle calibres. I know how to create a combo box that references a table on another sheet. I just don't know how to make the second conditional on the first. Anyone have a clue?

Thanks.

EDIT: I need to make about 100 individual sets of these that can all be diferent from each other.

[SPQR Renegade001]

If you can get away with it, you can use 2 cells to collect user input, one for each combo, and a 3rd cell that's a strcat of those. ie. c1= a1 & b1.
That 3rd cell can be way out in space where the user won't see it, or you can hide that col or row.  

[Gambler]

Okay, it's not completely pretty, but it can be done.

1)  Create your table of Firearms ie Pistol, Rifle, Machine Gun.

2)  Create a Separate table for each firearm listing all the calibers.  The tables can all be in the same worksheet.  Just keep them separate enough so that you can know what is what.  Also create a blank caliber table.  Each of your caliber tables needs to have the same number of rows.  If you don't have enough caliber, just leave the balance blank, but make sure that you don't put something else in there.

3)  Create your two combo boxes.  We'll call them combobox1 and combobox2.

4)  Combobox1 has the input range properties for your table of firearms, and a cell link to some place in the spreadsheet.

5)  Combobox2 has the input range set as the blank caliber table.

Now's where it gets tricky.

6)  Edit combobox1 and assign macro.  Do a record macro.  This starts the macro editor.  Stop it immediately without doing anything.  This will create an empty template for you to put the code in.

7)  Edit the combobox again and now edit the macro that was assigned to the combobox.  Put the following code into the macro

Code:

Sub DropDown4_Change()

  Select Case Range("c4").Value   'C4 is where I put the cell link in my sample
       
        Case 1
             Worksheets("Sheet2").Activate
            Range("c3:c4").Copy   'C3:c4  is where I put the data range in my sample
       
            Range("b3").PasteSpecial   'b3 is where I put the empty caliber table       
            Worksheets("sheet1").Activate
    
        Case 2
            Worksheets("Sheet2").Activate
            Range("d3:d4").Copy
            Range("b3").PasteSpecial
            Worksheets("sheet1").Activate
        Case Else
    End Select

End Sub

Now you can most likely get crazy and do named ranges for each type of firearm and reference those instead of the cell references.  Each case is just a different fire arm, and the number used is the sequence in the actual list.

[jualdeaux]

I am tying to make a special spreadsheet and I have no idea how to do something. It may not even be possible.

 I am trying to make a sheet that has a list where each item has two combo boxes. The first one has two items in it. The second one willbe conditional on what is choosen in the first box. So, an example might be where the first box has "pistol" and "rifle" as the options. If I choose pistol, the second box will have a listing of all the different pistol calibres in it. If I choose rifle, it will have all the rifle calibres. I know how to create a combo box that references a table on another sheet. I just don't know how to make the second conditional on the first. Anyone have a clue?

Thanks.

EDIT: I need to make about 100 individual sets of these that can all be diferent from each other.

[SPQR Renegade001]

If you can get away with it, you can use 2 cells to collect user input, one for each combo, and a 3rd cell that's a strcat of those. ie. c1= a1 & b1.
That 3rd cell can be way out in space where the user won't see it, or you can hide that col or row.  

[Gambler]

Okay, it's not completely pretty, but it can be done.

1)  Create your table of Firearms ie Pistol, Rifle, Machine Gun.

2)  Create a Separate table for each firearm listing all the calibers.  The tables can all be in the same worksheet.  Just keep them separate enough so that you can know what is what.  Also create a blank caliber table.  Each of your caliber tables needs to have the same number of rows.  If you don't have enough caliber, just leave the balance blank, but make sure that you don't put something else in there.

3)  Create your two combo boxes.  We'll call them combobox1 and combobox2.

4)  Combobox1 has the input range properties for your table of firearms, and a cell link to some place in the spreadsheet.

5)  Combobox2 has the input range set as the blank caliber table.

Now's where it gets tricky.

6)  Edit combobox1 and assign macro.  Do a record macro.  This starts the macro editor.  Stop it immediately without doing anything.  This will create an empty template for you to put the code in.

7)  Edit the combobox again and now edit the macro that was assigned to the combobox.  Put the following code into the macro

Code:

Sub DropDown4_Change()

  Select Case Range("c4").Value   'C4 is where I put the cell link in my sample
       
        Case 1
             Worksheets("Sheet2").Activate
            Range("c3:c4").Copy   'C3:c4  is where I put the data range in my sample
       
            Range("b3").PasteSpecial   'b3 is where I put the empty caliber table       
            Worksheets("sheet1").Activate
    
        Case 2
            Worksheets("Sheet2").Activate
            Range("d3:d4").Copy
            Range("b3").PasteSpecial
            Worksheets("sheet1").Activate
        Case Else
    End Select

End Sub

Now you can most likely get crazy and do named ranges for each type of firearm and reference those instead of the cell references.  Each case is just a different fire arm, and the number used is the sequence in the actual list.

[jualdeaux]

I am tying to make a special spreadsheet and I have no idea how to do something. It may not even be possible.

 I am trying to make a sheet that has a list where each item has two combo boxes. The first one has two items in it. The second one willbe conditional on what is choosen in the first box. So, an example might be where the first box has "pistol" and "rifle" as the options. If I choose pistol, the second box will have a listing of all the different pistol calibres in it. If I choose rifle, it will have all the rifle calibres. I know how to create a combo box that references a table on another sheet. I just don't know how to make the second conditional on the first. Anyone have a clue?

Thanks.

EDIT: I need to make about 100 individual sets of these that can all be diferent from each other.

[SPQR Renegade001]

If you can get away with it, you can use 2 cells to collect user input, one for each combo, and a 3rd cell that's a strcat of those. ie. c1= a1 & b1.
That 3rd cell can be way out in space where the user won't see it, or you can hide that col or row.  

[Gambler]

Okay, it's not completely pretty, but it can be done.

1)  Create your table of Firearms ie Pistol, Rifle, Machine Gun.

2)  Create a Separate table for each firearm listing all the calibers.  The tables can all be in the same worksheet.  Just keep them separate enough so that you can know what is what.  Also create a blank caliber table.  Each of your caliber tables needs to have the same number of rows.  If you don't have enough caliber, just leave the balance blank, but make sure that you don't put something else in there.

3)  Create your two combo boxes.  We'll call them combobox1 and combobox2.

4)  Combobox1 has the input range properties for your table of firearms, and a cell link to some place in the spreadsheet.

5)  Combobox2 has the input range set as the blank caliber table.

Now's where it gets tricky.

6)  Edit combobox1 and assign macro.  Do a record macro.  This starts the macro editor.  Stop it immediately without doing anything.  This will create an empty template for you to put the code in.

7)  Edit the combobox again and now edit the macro that was assigned to the combobox.  Put the following code into the macro

Code:

Sub DropDown4_Change()

  Select Case Range("c4").Value   'C4 is where I put the cell link in my sample
       
        Case 1
             Worksheets("Sheet2").Activate
            Range("c3:c4").Copy   'C3:c4  is where I put the data range in my sample
       
            Range("b3").PasteSpecial   'b3 is where I put the empty caliber table       
            Worksheets("sheet1").Activate
    
        Case 2
            Worksheets("Sheet2").Activate
            Range("d3:d4").Copy
            Range("b3").PasteSpecial
            Worksheets("sheet1").Activate
        Case Else
    End Select

End Sub

Now you can most likely get crazy and do named ranges for each type of firearm and reference those instead of the cell references.  Each case is just a different fire arm, and the number used is the sequence in the actual list.