I recently designed a data collection tool sent out to reps around the
country. Each rep was instructed to save their file with his/her State
abbreviation and send it back to me. So that means i have 50 files (1 for
each abbrev).

I need to pull all the data entered into one workbook that lists all the
States (each row contains the State Abbreviation in column A and to the right
of column A is all the fields from the data collection tool)... What would be
the best way of going about doing this? I am assuming there has to be some
kind of database query or something along those lines, just that I am not to
familiar with any feature like this. I tried using indirect.ext, but it only
works for one cell at a time. When I drag it down to other cells, it crashes
excel everytime.

Can someone point me in the right direction?

Ron de Bruin has lots of sample code:
http://www.rondebruin.nl/copy3.htm

IntricateFool wrote:

I recently designed a data collection tool sent out to reps around the
country. Each rep was instructed to save their file with his/her State
abbreviation and send it back to me. So that means i have 50 files (1 for
each abbrev).

I need to pull all the data entered into one workbook that lists all the
States (each row contains the State Abbreviation in column A and to the right
of column A is all the fields from the data collection tool)... What would be
the best way of going about doing this? I am assuming there has to be some
kind of database query or something along those lines, just that I am not to
familiar with any feature like this. I tried using indirect.ext, but it only
works for one cell at a time. When I drag it down to other cells, it crashes
excel everytime.

Can someone point me in the right direction?

--

Dave Peterson