@JHooverjsc2022
Is there a formula or macro that knows just which 60+ files to read your data from, which rows to examine, which columns contain the data important to you, and where that data is to be pasted into your company's spreadsheet? Of course not.
If your company is not willing to hire a programmer to create such a macro, prepare to create one yourself. Become familiar with the VBA editor (Alt+F11) and its child windows. Learn how variables are defined (usually with a Dim statement) and what the VBA native data types are (String, Integer, Long, Date, and more); learn what arrays are, and how individual array elements are referenced. Learn how to display a custom message in a dialog (the MsgBox function). Learn how to use text files (the Open, Close, and Line Input statements) and how the Split statement can create an array of strings from one string. Learn what the main Excel-supplied objects are, and how to write VBA code to use their properties and methods, and assign new values to cells. There are plenty of books on Excel VBA; if you don't want to buy one, check with your local library.
My impression is that your new macro (in a macro-enabled workbook) should include a spreadsheet that contains a list of the 60+ filenames. The macro should in turn:
- copy the value of a cell in that list into a variable;
- determine the fully-qualified file specification, and open that file (issuing a warning message about unavailable data if unsuccessful);
- read the necessary row(s);
- split the comma-separated values from a line of text (now in a variable) into an array of strings;
- assign the relevant values (in specific array entries) into specific cells in the target spreadsheet;
- close that file; and
- repeat those actions for each non-empty cell in the list.
All of that can be done even with old versions of Excel.
Important note: Test this macro by writing values to a spreadsheet that is not your production-data spreadsheet. Only after extensive testing, and preferably code review by someone else, should you start modifying the production-data spreadsheet.
