populating drop-down box in Excel from another workbook

[crawfme]

I am using Excel 2003 and have followed the steps on the Microsoft website at

http://office.microsoft.com/en-us/excel/HP052022151033.aspx

to populate a drop-down box with a list of values from another workbook, but I get an error message that says "You may not use references to other worksheets or workbooks for Data Validation criteria".

Anyone have any suggestions? I am naming the range and using an = sign to reference it.

Thanks.

 

[SkipVought]

Hi,

I have never tried to name a list in another workbook to use as a named range in this worbook. It apparently does not work in Excel 2003.

What I would do, and I ofter employ this technique, is to use MS Query to return a DISTINCT list to a new sheet, from the other workbook, name this list and use the named range in the Data Validation - List.

faq68-5829



Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[crawfme]

Thanks Skip. I've been playing with this and it's going to work.

The FAQ that you pointed me to says that you can both retrieve AND update data using query, but I can't seem to figure out the update part.

I have a spreadsheet that is a calculator. What I would like is to have a separate spreadsheet that would hold the history of the cases that I have run. I'll create a drop-down list that will allow the user to retrieve a case that has already been run, but I also want to be able to let them save a new case. Can I do this without having to open that other spreadsheet?

Thanks.

 

[SkipVought]

How are you trying to update?

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[crawfme]

Currently I have the history file open and I have a macro that I run from the calculator that reads the data from the calculator and cuts and pastes the results as values into the history file. What I'm hoping I'll be able to do is not have to open the history file at all. If the user wanted to retrieve an existing case, the query will retrieve the data from the history file. But if they create an entirely new case, I'd like to be able to copy the data out to the history file.

 

[SkipVought]

You must use an UPDATE query, with the proper criteria. Here is some sample code. If you need help, please post your questions in Forum707.

Sub UpdateT()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "UPDATE `" & sPath & "\" & sDB & "`.`Sheet1$`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "Set `Sheet1$`.WORK_PHONE='" & [NewPhone] & "'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE (`Sheet1$`.CLOCK_ID='36250')"

    On Error Resume Next

    With Sheet2.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh False
    End With
    
    On Error GoTo 0
End Sub

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]