Create table based off drop down box selection

[jonnynacker]

Hey All,

Im trying to create a table based off values in another reference worksheet which will be generated from a value selected from a drop down box.

The values in this newly generated table is then to have a dropdown box next to them in which W or L can be selected.

the link below contains the same question with the attached spreadsheet

Populate Table from database based on defined value

Any help will be very much appreciated!

 

[L. Howard]

I might be able to make that happen...

So, once you select a B3 drop down "LETTER", use the generated list of names to make a Name/Hours/OP Number matrix on another sheet?

And when you select another drop down "LETTER" where does that matrix go? Replace the previous matrix or put it a row or two below the last one or to the right of the last one?

I will assume that a non-merged cell equivalent will be acceptable, as I prefer to follow the advice of many of the pro's who advise against merged cell in tables and such. There are other means to achieve the look you want per your box example.

Howard

 

[jonnynacker]

thats correct, the list generated will then create matrix on sheet 3, with the name being in D4, Hours in E4 and OP Number in E5. Next name being in D6, hours in E6 and OP Number in E7 and so on until all names which are generated in Sheet1 are across the row.
when you select another letter it will replace the previous matrix.
Yeah non merged is fine, I've also been trying to firgure it out and have read that I shouldnt be using merge.

Thanks Howard

 

[L. Howard]

thats correct, the list generated will then create matrix on sheet 3, with the name being in D4, Hours in E4 and OP Number in E5. Next name being in D6, hours in E6 and OP Number in E7 and so on until all names which are generated in Sheet1 are across the row.
when you select another letter it will replace the previous matrix.
Yeah non merged is fine, I've also been trying to firgure it out and have read that I shouldnt be using merge.

Thanks Howard

I'm thinking you mean the following:

The name "ZAC" centered on the cells D4 & E4
"Hours" in cell D5
"OP Number" in E5

The name "Brent" centered on the cells F4 & G4
"Hours" in cell F5
"OP Number" in G5

Repeat to end of name list.

Howard

 

[jonnynacker]

That's correct

 

[L. Howard]

Okay, I'll see what I can do.

Howard

 

[L. Howard]

Try this, I think it gets it done.

Here is the code and the link has the workbook with the code incorporated in it.

Howard

https://www.dropbox.com/s/w4klpmrtaz8057v/Book3 EXP DBx.xlsm?dl=0

Sub Name_Hours_OPNumber()

Dim vardata As Variant
Dim i As Long, n As Long

Application.ScreenUpdating = False

With Sheets("Sheet3").Range("4:5")
   .ClearContents
   .HorizontalAlignment = xlGeneral
   .VerticalAlignment = xlBottom
   .WrapText = True
End With

With Sheets("Sheet1")
    vardata = .Range("D7:D" & .Cells(Rows.Count, "D").End(xlUp).Row)
End With

n = 4

For i = LBound(vardata) To UBound(vardata)

    With Sheets("Sheet3")
        .Activate
        .Cells(4, n) = vardata(i, 1)
        .Cells(5, n) = "Hours"
        .Cells(5, n + 1) = "OP Number"
        .Range(Cells(4, n), Cells(4, n + 1)).Select
        Selection.HorizontalAlignment = xlCenterAcrossSelection
    End With
    
    n = n + 2
Next
Application.Goto Range("D6")

Application.ScreenUpdating = True
End Sub

 

[jonnynacker]

BRILLIANT! Thank you so much Howard

I forgot that the only names that are to be brought over to sheet 3 are meant to be the names with a "W" selected from the drop down box, also the coordinator selected in sheet1 cell B5. Is that modification to the code hard?

 

[L. Howard]

BRILLIANT! Thank you so much Howard

I forgot that the only names that are to be brought over to sheet 3 are meant to be the names with a "W" selected from the drop down box, also the coordinator selected in sheet1 cell B5. Is that modification to the code hard?

With the workbook example in the link I posted:

If you want the drop down that is in cell B3 to be in cell B5 of sheet 1,, then select B3 (click on B3) and grab the edge of the cell with a 'left-click-and-hold' and drag to cell B5.

Then change this line of code in the Sheet 1 code module to refer to cell $B$5 instead of B3.

If Target.Address <> "[COLOR="#FF0000"]$B$5[/COLOR]" Then Exit Sub

I don't understand the "W" selection issue. Are you talking about the W in column F on sheet 1?

Need more detail on the "W" caper.

Howard

 

[jonnynacker]

Yeah sorry that was quite vague.

I still want cell B3 on sheet1 to determine the list generated on sheet1, No change to that.

the Horizontal list generated in sheet3 which contains the names, Hours and OP Number I wish to be dependant on the value of the drop downbox generated in column F on sheet1. If the cell contains "W", then the name is to be in the Horizontal list generated in sheet 3, If it contains "L" then they are not to br brought across in to sheet3.

Also, the name which is input in to cell B5 on sheet1, the "COORDINATOR". Is to be the first name generated in the horizontal list generated in Sheet3 along with "hours" and "OP Number".

I hope this makes sense?

 

[L. Howard]

1. Does this book do this part correctly? (The part in the quote below)

https://www.dropbox.com/s/w4klpmrtaz8057v/Book3 EXP DBx.xlsm?dl=0

I still want cell B3 on sheet1 to determine the list generated on sheet1, No change to that.

2. Check sheet 3, nothing is done there from the drop down in B3.

3. If so, then where what when is the drop down in B5 supposed to come from?

AND

4. Who what where do the "W" & "L" for column F come from?

Howard