- 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.
View attachment 45573
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.
Hi Bobsan,
I was searching for a solution to this very problem and this has worked perfectly! I stripped out some of the cell dependant forumla as I only wanted one cell with the filename controlling it and have ended up with:
Private Sub CommandButton1_Click()
'#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("tbRefs4")
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 = "T:\CUSTOMER LIBRARY\2 Quote Log" & Application.PathSeparator 'get file path
strf = "'" & strf & "[" & .Cells(1, 1) & "]" 'Add filename
strf = strf & "Batch'!" 'Add sheet name
strf = strf & "$Y$5" 'Add cell address
Select Case LCase(.Cells(1, 100).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, 2).Formula = strf
End With
Next i
ep:
On Error Resume Next
Set li = Nothing
Set cc = Nothing
End Sub
Theres one additional bit that I would love if you could help me with. I'd like to have the option to create more forumlas at the same time. For example in the 3rd column, to have the same pathway as before but pull the data from cell L21 ( strf = strf & "$L$21" 'Add cell address). Hopefully this is something that can be done? If I could expand it further to have the 4th column as cell D5 etc etc, would be fantastic!
