Validation list with non blanks from different column

[Shaggyjh]

I have a spreadsheet containing a worksheet (Applications) that has a
reference number E1,E2,E3 etc in column A in cells A3:A102. Cells C3:1C102
will contain a name but may not.

I want to create a Validation ldrop down list on another worksheet (Menu)
that only contains the reference number A3:A102 if there is a value in the
cell C3:C102.

Is there anyway of doing this?

At the moment my validation list contains blanks, as i have put an if
formula in the A3:A103 range to put blank if the C3:C102 cell is blank.

 

[Gary''s Student]

First set an AutoFilter on column C to hide the blanks (rows without names).
Then copy column A to you destination sheet and use it for validation.

 

[Shaggyjh]

I think i can write the macro to do the autofilter but how do i get the
resulting list to copy to my destination sheet?

Many thanks

 

[Gary''s Student]

Here is some quick code from the Recorder:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/1/2009 by James Ravenswood
'

'
Range("C1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="<>"
Range("A1:A27").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
End Sub

I would modify this by replacing A27 with something REALLY bid, like A10000.
Also after the Sheets("Sheet2").Select, put something like:

Range("B9").Select

to set the destination cell.