Creating summary file from multiple workbooks created on specific day

Rheannon

New Member
Joined
Mar 8, 2017
Messages
10
Greetings all,
I have an analytical piece of equipment that exports data for each sample analyzed into a separate .txt file. I would like to compile all of the data from analyses performed on one day into one summary workbook.
All of the exports are in the same format but the location of the cells for the required information can vary. Specifically, these are the 10 required columns:

Columns A:D will be in the same position in all the files:
A='Sheet1'!$A$19
B='Sheet1'!$C$19
C='Sheet1'!$D$19
D='Sheet1'!$C$23

Columns E:J will not be cell-specific but can be located in each file:
E=INDIRECT("'Sheet1'!C"&Sheet1!O2)
F=INDIRECT("'Sheet1'!D"&Sheet1!O2)
G=INDEX('Sheet1'!A23:A264,MATCH("$S props V2",'Sheet1'!A23:A264,0)+1,1)
H=INDEX('Sheet1'!E23:E264,MATCH("$S props V2",'Sheet1'!A23:A264,0)+1,1)
I=INDEX('Sheet1'!B23:B264,MATCH("N",'Sheet1'!A23:A264,0),1)
J=INDEX('Sheet1'!B23:B264,MATCH("PROTEIN",'Sheet1'!A23:A264,0),1)

My questions are:

1) Will Excel be able to scan and interpret a .txt file without being converted first? Or would each file have to be saved as an excel-compatible format before proceeding? (unfortunately, for whatever reason, the software will only export as .txt)

2) Would there be a way on a new workbook to input the desired analysis date and then the target folder would search any files created on that date and the respective data would be extracted?

3) Is it even possible to extract the data with this set of requirements since they're not all in the same target cell?

Thank you so much in advance and please let me know if there's any further info you require!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top