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

"Kanmi" wrote in message
...
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" wrote:

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:F 20"))

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

"Kanmi" wrote in message
...
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.