I have this spreadsheet that I wanta column of cells to have a drop down box
that would show choices froma text file that periodically gets updated. I
would like the drop down box to "point" to this external file to get it's
data. Is this possible?

Maybe you could have a routine that opens the text file and updates an existing
sheet (maybe even hide that sheet) when the workbook opens.

I created a worksheet named Table, then used Insert|name to create a name that
changed with the amount of data in column A.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

Then used that range name as the list in a Data|Validation cell.
http://www.contextures.com/xlDataVal01.html
(also from Debra's site).

Then I could use a macro that updated worksheet:

Option Explicit
Sub Auto_Open()
Dim myTextFileName As String
Dim TextWks As Worksheet
Dim TableWks As Worksheet

Set TableWks = ThisWorkbook.Worksheets("Table")

myTextFileName = "C:\myfolder\text.txt"
myTextFileName = "C:\My Documents\Excel\spacedelim.txt"

Workbooks.OpenText Filename:=myTextFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1))

Set TextWks = ActiveSheet

'clean up existing values
TableWks.Cells.Clear

'copy new values
TextWks.Range("A:A").Copy _
Destination:=TableWks.Range("a1")

'close the text file
TextWks.Parent.Close savechanges:=False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

kspeters wrote:

I have this spreadsheet that I wanta column of cells to have a drop down box
that would show choices froma text file that periodically gets updated. I
would like the drop down box to "point" to this external file to get it's
data. Is this possible?

--

Dave Peterson

Thansk Dave I will take a look at this.

"Dave Peterson" wrote:

Maybe you could have a routine that opens the text file and updates an existing
sheet (maybe even hide that sheet) when the workbook opens.

I created a worksheet named Table, then used Insert|name to create a name that
changed with the amount of data in column A.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

Then used that range name as the list in a Data|Validation cell.
http://www.contextures.com/xlDataVal01.html
(also from Debra's site).

Then I could use a macro that updated worksheet:

Option Explicit
Sub Auto_Open()
Dim myTextFileName As String
Dim TextWks As Worksheet
Dim TableWks As Worksheet

Set TableWks = ThisWorkbook.Worksheets("Table")

myTextFileName = "C:\myfolder\text.txt"
myTextFileName = "C:\My Documents\Excel\spacedelim.txt"

Workbooks.OpenText Filename:=myTextFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1))

Set TextWks = ActiveSheet

'clean up existing values
TableWks.Cells.Clear

'copy new values
TextWks.Range("A:A").Copy _
Destination:=TableWks.Range("a1")

'close the text file
TextWks.Parent.Close savechanges:=False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

kspeters wrote:

I have this spreadsheet that I wanta column of cells to have a drop down box
that would show choices froma text file that periodically gets updated. I
would like the drop down box to "point" to this external file to get it's
data. Is this possible?

--

Dave Peterson