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 would look at the workbook, the site you mention requires a log on to a link utility.

Can you just post the actual link to the workbook here?

Howard

 

[jonnynacker]

I can't seem to be able to attach documents in this forum, try this link;

Populate Table from database based on defined value

Cheers

 

[L. Howard]

I can't seem to be able to attach documents in this forum, try this link;

Populate Table from database based on defined value

Cheers

I get the same site as in the previous post.

You cannot download documents here, but you can post links to them.

If you can save your workbook to a utility like Drop Box, which I use, then you can post a link here.

Not sure I will have a solution to the workbook issue, but am willing to look at it.

Howard

 

[jonnynacker]

the webpage should direct you to another forum I posted in, in which there should be a linked file.

If this doesnt work, i tried this dropbox file share, let me know if this works;

https://www.dropbox.com/s/80guvy9nj0ypohn/Get Started with Dropbox.pdf?dl=0

thanks alot!

 

[jonnynacker]

sorry try this one;

https://www.dropbox.com/s/90begsd0jqxa69l/Copy of FieldShiftReport.xlsm?dl=0

 

[L. Howard]

On the first link, I get an invitation to sign up for OzGrid.

The second one offers to me a subscription to Drop Box (which I already have).

I can find no link to an Excel workbook at either.

Howard

 

[L. Howard]

Got it, will take a look.

Howard

 

[L. Howard]

Here is a stab at what I think you want.

Where you select a LETTER in cell B3 and that letter list is compiled from the data in sheet 2, column E, F, G into sheet 1 column D and E and "W/L" drop downs in in column F.

Howard


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

In the sheet 1 module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub

Select Case Target.Value

Case Is = "A"
  'MsgBox "A"
  Run "Select_A"
  
Case Is = "B"
  'MsgBox "B"
  Run "Select_B"
  
Case Is = "C"
  'MsgBox "C"
  Run "Select_C"
  
Case Is = "D"
   'MsgBox "D"
   Run "Select_D"
   
End Select

End Sub

In the standard module (Insert > Module):

Option Explicit

Sub Drop_Down_WL()

Dim OneRng As Range

  Set OneRng = Sheets("Sheet1").Range("F7:F" & Cells(Rows.Count, "D").End(xlUp).Row)
  
With OneRng.Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="W,L"
  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = ""
  .ErrorTitle = ""
  .InputMessage = ""
  .ErrorMessage = ""
  .ShowInput = True
  .ShowError = True
End With

End Sub


Sub Select_A()

Dim c As Range
Application.ScreenUpdating = False

With Range("F:F").Validation
  .Delete
End With

With Range("D7", Range("F7").End(xlDown))
  .ClearContents
End With

Sheets("Sheet2").Activate

For Each c In Sheets("Sheet2").Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    If c.Value = "A" Then
    c.Offset(, 1).Resize(1, 2).Copy Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2)
    End If
Next

Sheets("Sheet1").Activate
Drop_Down_WL

Application.ScreenUpdating = True

[F7].Activate
End Sub


Sub Select_B()

Dim c As Range
Application.ScreenUpdating = False

With Range("F:F").Validation
  .Delete
End With

With Range("D7", Range("F7").End(xlDown))
  .ClearContents
End With

Sheets("Sheet2").Activate

For Each c In Sheets("Sheet2").Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    If c.Value = "B" Then
    c.Offset(, 1).Resize(1, 2).Copy Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2)
    End If
Next

Sheets("Sheet1").Activate
Drop_Down_WL

Application.ScreenUpdating = True

[F7].Activate
End Sub


Sub Select_C()

Dim c As Range
Application.ScreenUpdating = False

With Range("F:F").Validation
  .Delete
End With

With Range("D7", Range("F7").End(xlDown))
  .ClearContents
End With

Sheets("Sheet2").Activate

For Each c In Sheets("Sheet2").Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    If c.Value = "C" Then
    c.Offset(, 1).Resize(1, 2).Copy Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2)
    End If
Next

Sheets("Sheet1").Activate
Drop_Down_WL

Application.ScreenUpdating = True

[F7].Activate

End Sub


Sub Select_D()

Dim c As Range
Application.ScreenUpdating = False

With Range("F:F").Validation
  .Delete
End With

With Range("D7", Range("F7").End(xlDown))
  .ClearContents
End With

Sheets("Sheet2").Activate

For Each c In Sheets("Sheet2").Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    If c.Value = "D" Then
    c.Offset(, 1).Resize(1, 2).Copy Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2)
    End If
Next

Sheets("Sheet1").Activate
Drop_Down_WL

Application.ScreenUpdating = True

[F7].Activate

End Sub

 

[jonnynacker]

IT WORKED!

thank you so much Howard, you truly are the king of kings.

would you know what line of code would need to be added to also make the names selected from the crew appear across a row in another sheet, merged to the cell next to them with the two cells under containing "hours" & "Operation Number".

E.g. in sheet 1 under employee name (They may modify the list daily so it has to be referenced off the generated list and not the referenced standard)

EMPLOYEE NAME
zac
brent
darryl
damien


to then be generated on another sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ZAC[/TD]
[TD]BRENT[/TD]
[TD]DARRYL[/TD]
[TD]DAMIEN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]HOURS[/TD]
[TD]OP Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]HOURS[/TD]
[TD]OP Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]HOURS[/TD]
[TD]OP Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]HOURS[/TD]
[TD]OP Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Thank you so much again!