User-inserted external file path used to pull cell values

[beerheart]

I have an organization that wants each vendor to fill out and submit a
net present value worksheet as part of the RFP process. Call it the
NPV workbook. The organization wants to then pull data from each of
those worksheets into a summary worksheet.

I would like for the organization to be able to input the file path for
each NPV workbook. Then the summary cells would use the file path to
pull the data.

What I have been trying so far isn't working:
1) Organization creates a hyperlink to each NPV workbook; example: Cell
B8 contains the hyperlinked string: F:\Emergent\Projects\03109 GSA
SIS\SIS Model 2.xls
2) Use CONCATENATE function to create command; example:
=CONCATENATE(B8,"\[SIS Model 2.xls]Offeror Worksheet'!$E$14")

What I get on step 2 is a well-formed string:
='F:\Emergent\Projects\03109 GSA SIS\[SIS Model 2.xls]Offeror
Worksheet'!$E$14

How do I get that string to execute as a command, as if I had typed
that exact string into the cell myself, rather than using the
concatenate formula to create it?

 

[Dick Kusleika]

The INDIRECT function will do that, but only if the other workbook is open.
Probably not what you want. You can create a macro that will build the
formula for you and put in the cells. Post back if a macro interests you.

 

[beerheart]

Thanks for the reply. I am interested in exploring a macro. I'll have
to take some time to look into creating them, though, as I never have
done so in Excel.

 

[Dick Kusleika]

Bh

Here's something to help you get started. First, read this

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Then use this macro as a starting point. This assumes that you select a
range where you want the formulas to be. Row 1 above each cell will be the
filename. Each cell you select will contain the cell address in the linked
sheet. So if in D3 of your summary sheet, you want to link to E14 in the
linked sheet, then you would have E14 in D3 (not =E14, just the text).

Have a go at it and post back if you need more help.

Sub MakeFormulas()

Dim cell As Range
Dim NewForm As String
Dim i As Long
Dim FNStart As Long
Dim HLRange As Range

i = 1

'Loop through the cells that you have selected
For Each cell In Selection.Cells

'Start building a string that will be the formula
NewForm = "='"

'Set a variable to the cell with the hyperlink
Set HLRange = cell.Parent.Cells(1, cell.Column)

'Find the last "\" (the start of the file name)
Do Until i = 0
FNStart = i
i = InStr(i + 1, HLRange.Value, "\")
Loop

'Add everything up until the filename
NewForm = NewForm & Left(HLRange.Value, FNStart)

'Open brace
NewForm = NewForm & "["

'Filename
NewForm = NewForm & Mid(HLRange.Value, FNStart + 1, 255)

'Close brace
NewForm = NewForm & "]"

'Add Sheet name
NewForm = NewForm & "BS Combined'!"

'Cell value contains reference to cell e.g. E14
NewForm = NewForm & cell.Value

'Put the new formula in the cell
cell.Formula = NewForm

Next cell

End Sub