I have a central location that contains 96 xls files; each of these 96 files represent an individual test located here:
"\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME"
I created an Excel workbook to input all the information of each individual test on to a single sheet. Then, I used a VBA to pull the names of the files and add a link to the file on a second sheet on the same workbook. I linked those files to sheet 1.
Next, I added all the information on the top row corresponding to the test performed broken down in to a single line per workbook (Test).
Now I need to populate sheet 1 on the new workbook (Advanced Main board test log.xlsx)
So far this formula works, but only for open workbooks:
=INDIRECT("'\\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME\["&A7&"]Summary'!$E9")
Where A7 represents the column of filenames which ranges from A6 to A103
Summary E9 is the cell I would like to enter in cell C7
Please note the populated values, this is because I have those 2 work books open, All #REF! cells contain the same formula as the populated cells.
please let me know if you need any additional information to accomplish this tedious task
I also tried doing Microsoft recommended method:
='[20160613 1002257 35000010-01B.xls]Summary'!$E9
replaced file name with cell containing file name:
='["&A7&"]Summary'!$E9
and I get #REF! in the cell and formula bar automatically changes to:
='(\\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME\["&A7&"]Summary'!$E9
According to Microsoft, the Indirect() function must have the workbook opened. You could cycle through the directory with VBA to get the cell values using this route.
Got a UDF and below is how you'd add it:
Open Visual Basic editor in excel (Development tab of the Ribbon - you may need to go into Excel options to unhide it).
Right click VBA Project([Your workbookname]), highlight insert and then click module.
Paste the below in module1 and save the workbook as a .xlsm.
Code:
Function GetField(Path As String, WorksheetName As String, CellRange As String) As Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wb = GetObject(Path)
Set ws = wb.Worksheets(WorksheetName)
Set rng = ws.Range(CellRange)
Application.DisplayAlerts = False
wb.Saved = True
wb.Close SaveChanges:=False
Application.DisplayAlerts = True
End Function
You can then use =GetField() in this workbook only.
Parameters: =getfield("\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME\"&A6,"Summary","E9")
Only tested with one file, but you should be able to use cell references, filenames, etc in rows/columns for more than one.
-Confirmed this works on multiple Rows/Columns
Edit: Changed how workbooks are closed from some code provided by Alistair Weir in another issue.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments