Linking Excel to a text file database

[Roger on Excel]

I use vlookup to access data from within my spreadsheet. The extensive
dataset I have is on one of the sheets and is large and cumbersome.

I understand that it is possible to link my sheet to an ancillary text file
within which i can store my database.

How does one go about doing this and will vlookup work?

Can anyone help or redirect me?

Thanks,

Roger

 

[Joel]

You m,ay be able to save the one worksheet in CSV format. Then link to the
CSV file. You can create the CSV file by just performing a SAVEAS selecting
CSV as the file type. A warning will come up saying that you will loose the
format and that only one sheet at a time can be saved in this format.

A CSV file is both a text and a Excel file as the same time. The CSV is
COMMA SEPERATED VALUES which uses commas to sperate data from one column to
the next like

abc, def, ghi
next row data, jkl,mno,pqr

abc will go into column A.
def will go into column B.
ghi will go into column C.
next row data will go into row 2 Column A.
jkl will go into row 2 Column B.
mno will go into Row 2 Column C
pqr will go into row 2 column D.

 

[Roger on Excel]

Thanks Joel,

I will give it a try

Roger

You m,ay be able to save the one worksheet in CSV format. Then link to the
CSV file. You can create the CSV file by just performing a SAVEAS selecting
CSV as the file type. A warning will come up saying that you will loose the
format and that only one sheet at a time can be saved in this format.

A CSV file is both a text and a Excel file as the same time. The CSV is
COMMA SEPERATED VALUES which uses commas to sperate data from one column to
the next like

abc, def, ghi
next row data, jkl,mno,pqr

abc will go into column A.
def will go into column B.
ghi will go into column C.
next row data will go into row 2 Column A.
jkl will go into row 2 Column B.
mno will go into Row 2 Column C
pqr will go into row 2 column D.

 

[RB Smissaert]

Although running SQL on text files is amazingly fast (considering there are
no indexes) there might be an advantage
to move to a real database like SQLite. Very simple, very fast and very good
free VB(A) wrapper available.
Not sure you were thinking of using SQL, but I think when your lookups are
getting complex using SQL will make
it a lot simpler.

RBS