Data Validation for drop down lists

[Guest]

I am trying to create a drop down list in workbook ProjectFile to select a Project Number from another workbook called ProjectList.

I have defined and named the range in ProjectList as ProjectNo =OFFSET(Project List!$C$1,0,0,COUNTC(Project Lit!$C:$C),1) as the list will keep growing.

I have then gone into ProjectFile and defined and named the list as MyList=projectlist.xls!ProjectNo.

However when I go into data validation select source as =MyList, I get and error message "the source currently evlauates to an error."

I have gone to the following sites http://www.contextures.com/tiptech.html, as suggested in responses to other queries, followed the direct steps and still cannot stop the error message.

If my list I am looking at is a concatenation of other columns could this be the problem??

Your suggestions would be most appreciated

 

[JulieD]

Hi Kes

did you retype the ProjectNo reference or copy & paste? if copy & paste
check out the countc (?) function syntax.

other than that the only other helpful thing i can say is that the
projectlist file needs to be open when doing the data validation (AFAIK).

hope this helps
Cheers
JulieD

I am trying to create a drop down list in workbook ProjectFile to select a

Project Number from another workbook called ProjectList.

I have defined and named the range in ProjectList as ProjectNo

=OFFSET(Project List!$C$1,0,0,COUNTC(Project Lit!$C:$C),1) as the list will
keep growing.

I have then gone into ProjectFile and defined and named the list as MyList=projectlist.xls!ProjectNo.

However when I go into data validation select source as =MyList, I get and

error message "the source currently evlauates to an error."

I have gone to the following sites

http://www.contextures.com/tiptech.html, as suggested in responses to other
queries, followed the direct steps and still cannot stop the error message.

 

[Ed Ferrero]

Hi kes

I have just put a tutorial at http://edferrero.m6.net/DataTutor1.html that
deals with this issue.

You might need to change
=OFFSET(Project List!$C$1,0,0,COUNTC(Project Lit!$C:$C),1)
to
=OFFSET(Project List!$C$1,0,0,COUNTA(Project Lits!$C:$C),1)

Ed Ferrero

I am trying to create a drop down list in workbook ProjectFile to select a

Project Number from another workbook called ProjectList.

I have defined and named the range in ProjectList as ProjectNo

=OFFSET(Project List!$C$1,0,0,COUNTC(Project Lit!$C:$C),1) as the list will
keep growing.

I have then gone into ProjectFile and defined and named the list as MyList=projectlist.xls!ProjectNo.

However when I go into data validation select source as =MyList, I get and

error message "the source currently evlauates to an error."

I have gone to the following sites

http://www.contextures.com/tiptech.html, as suggested in responses to other
queries, followed the direct steps and still cannot stop the error message.

 

[Arvi Laanemets]

Hi

Into ProjectFile workbook, add a worksheet ProjectList
Mirror your list on ProjectList workbook into this new file, like
=IF('C:\My Documents\[ProjectList.xls]ProjectList'!C1="","",'C:\My
Documents\[ProjectList.xls]ProjectList'!C1)
NB! Copy this formula down so you have enough emty rows ready for future
entries.
Create your dynamic named range in ProjectFile, based on mirrored
ProjectList. NB! You have to modify it, so formulas returning empty string
are excluded. Try
=OFFSET(Project List!$C$1,0,0,COUNTIF(Project List!$C:$C,">"&""),1)

I am trying to create a drop down list in workbook ProjectFile to select a

Project Number from another workbook called ProjectList.

I have defined and named the range in ProjectList as ProjectNo

=OFFSET(Project List!$C$1,0,0,COUNTC(Project Lit!$C:$C),1) as the list will
keep growing.

I have then gone into ProjectFile and defined and named the list as MyList=projectlist.xls!ProjectNo.

However when I go into data validation select source as =MyList, I get and

error message "the source currently evlauates to an error."

I have gone to the following sites

http://www.contextures.com/tiptech.html, as suggested in responses to other
queries, followed the direct steps and still cannot stop the error message.