Validation

[Kanmi]

Please how can i use validate dropdown menu data and link to some set of data
on another worksheet. I have dropdown menu "OFC and DOWNSTRM" I mean when i
select OFC then it should retrieve information for OFC and when i select
DOWNSTRM then it should retrieve information for DOWNSTRM . Please can
anybody advice on what to do?. Thanks. Appreciate your time.

 

[stanleydgromjr]

Kanmi,

You could use the Worksheet_Change Event that will be triggered whe
you make a choice from your drop down list.

Please post your workbook (or a sample) - scroll down and see "Manag
Attachments"

 

[Kanmi]

How do i go about Worksheet_Change Event?. Thanks for your respond.

 

[Patrick Molloy]

not sure what you mean by retrieving information

so if you have a range selected on your main sheet, enter this as a formula
array

=INDIRECT(IF(C6="OFC","Sheet1!A1:C20","Sheet2!D1:F20"))

if select one of two 20x3 tables depending on the validated cell - in my
example, this is C6

 

[Kanmi]

Compile Error Expected sub. Hoping this code will do what i want but it
giving error. aadvice

Private Sub CommandButton1_Click()
ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
If ask = 7 Then Exit Sub

Select Case Range("D21")
Case "DOWNSTRM":
Set TrainingSht = Sheets("Training_Progress_By_Employee")
Set PersonnelSht = Sheets("Personnel Roster")
Set LookupSht = Sheets("Lookup")

Case "OFC":
Set TrainingSht = Sheets("Training_Progress_By_Employee2")
Set PersonnelSht = Sheets("Personnel Roster2")
Set LookupSht = Sheets("Lookup2")

End Select
Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)
MsgBox "Done", , BoxTitle
End Sub

 

[Patrick Molloy]

change this

Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)

to this
BuildDetailReport TrainingSht, PersonnelSht, LookupSht


reason: you have two sub definitions but only terminate the second with the
End Sub statement. I've already asked you to and an End Sub immdiately after
your End Select, but you said that doesn';t work. my guess is that after
your select statement block yo intend to call the report builder, so it
needs to have the correct syntax, which I hope is now correct.

If this does not work, please show your full code as it currently is, and
state what error messages you get and where they occur

thanks
Patrick