- I hote the curly brackets do not indicate array formulas
- You dont really need SUM function to get a cell's value
This is what I came up with: you setup a table, name it
tbRefs and a button to extract the data from various workbooks.
Like I said in my previous post the idea is to construct the formula and leave Excel take the values out of any closed workbook, if it is acessible.
So in the last column of the table the necessary formulas will be written and the values will turn up if everything is correct. Once you have the values you can use them anywhere in your workbook.
To automate the process create a button next to the table (on the same sheet) and make it run the following sub (put this in a public module):
VBA Code:
Option Explicit
Option Compare Text
Sub MakeFormulas()
'#Creates formulas to refer to external file based on provided references to the data
'# no error checking is implemented - the formula will return an error if references are incorrect
Dim li As ListObject, cc As Range
Dim i As Long, j As Long, strf As String
On Error Resume Next
Set li = ThisWorkbook.ActiveSheet.ListObjects("tbRefs")
If li Is Nothing Then GoTo ep
If li.ListRows.Count < 1 Then GoTo ep
For i = 1 To li.ListRows.Count
With li.ListRows(i).Range
strf = .Cells(1, 1).Value & Application.PathSeparator 'get file path
strf = "'" & strf & "[" & .Cells(1, 2) & "]" 'Add filename
strf = strf & .Cells(1, 3) & "'!" 'Add sheet name
strf = strf & .Cells(1, 4) 'Add cell address
Select Case LCase(.Cells(1, 5).Value) 'This will endable you to provide different types of formulas for different references
Case "" 'Just a reference to the cell
strf = "=" & strf 'make formula - just a reference to a cell value
Case "sum"
strf = "=SUM(" & strf & ")" 'Anodther formula - this enables you to provide range instead of cell. However this option will return 0 if the cell contains text
Case Else
'More options can be included - the sky is the limit :)
strf = ""
End Select
.Cells(1, 6).Formula = strf
End With
Next i
ep:
On Error Resume Next
Set li = Nothing
Set cc = Nothing
End Sub
| MrExcel Playbook 01 2021-08.xlsm |
|---|
|
|---|
| A | B | C | D | E | F | G |
|---|
| 1 | Path | File | Sheet | Cell | Formula | Value | |
|---|
| 2 | c:\Users\bobsa\Desktop | Workbook1.xlsx | Sheet1 | B2 | | B24546 | |
|---|
| 3 | c:\Users\bobsa\Desktop | Workbook1.xlsx | Sheet1 | i4 | | Paul Oakley | |
|---|
| 4 | c:\Users\bobsa\Desktop | Workbook1.xlsx | Sheet1 | T3 | | 0.333333333 | |
|---|
| 5 | c:\Users\bobsa\Desktop | Workbook4.xlsx | Sheet 2222 | P1:P3 | SUM | 750 | |
|---|
| 6 | c:\Users\bobsa\Desktop | Workbook4.xlsx | Sheet 2222 | D3 | | Warehouse | |
|---|
| 7 | | | | | | | |
|---|
|
|---|
if the cell in column E (Formula) is:
- empty - you will get a simple cell reference as a formula
- SUM - the formula will use SUM(), in this case you can provide a range reference like A5:B15 in the Cell column (D)
- you can add more options to this one - then you have to write an additional CASE statement in the sub
Whenever you change a vlaue in columns A to E push the button to update column F.
You will get a popup message from Excel if you provide a non-existent sheet name for a file.
Give it a try.
