AddName - issues w/ creating with reference to a diffferent workbook.

[Mark J Kubicki]

trying to create a named range for use as validation list in a cell drop
down
the range is in a different workbook

....can't even get the basics right -UGH!


Private Sub Workbook_Open()
Const ProjectDataFile = "Job Nos for Timesheets.xls"
Const ProjectDataFilePath = "E:\"

ActiveWorkbook.Names.Add Name:="ProjectNames", _
RefersToR1C1:="=workbooks(ProjectDataFilePath &
ProjectDataFile).worksheets(Project List).range($F$2:$F500)"


thanks in advance, mark

 

[Tom Ogilvy]

='[Mybooks.xls]Sheet1'!$A$1:$A$20

Data validation won't work with a closed workbook anyway.

 

[Mark J Kubicki]

not to worry, i do have the workbook opened (as hidden)

but how do i get the workbook name, as a variable, into the code... this is
where all the troubles began!
(also, i suspect that as long as the workbook is open, it's location (drive
and path) are irrelevant? correct?)

='[Mybooks.xls]Sheet1'!$A$1:$A$20

Data validation won't work with a closed workbook anyway.

--
Regards,
Tom Ogilvy

trying to create a named range for use as validation list in a cell drop
down
the range is in a different workbook

...can't even get the basics right -UGH!


Private Sub Workbook_Open()
Const ProjectDataFile = "Job Nos for Timesheets.xls"
Const ProjectDataFilePath = "E:\"

ActiveWorkbook.Names.Add Name:="ProjectNames", _
RefersToR1C1:="=workbooks(ProjectDataFilePath &
ProjectDataFile).worksheets(Project List).range($F$2:$F500)"


thanks in advance, mark

 

[Tom Ogilvy]

ActiveWorkbook.Names.Add Name:="ProjectNames", _
RefersToR1C1:="='[" & ProjectDataFile & "]" & ProjectList & _
"'!$F$2:$F500"

I assume ProjectList was a variable, but if it is the actual name, just make
it a variable and set it to

ProjectList = "Project List"

The path would not be needed.