Calculate External References (Excel 97-SR2)

Topic

New Reply

Is there any way I can ‘calculate’ external references?

=’n:path1path2path3Excel[Text Function2.xls]Sheet1′!$D$11

Will work if.

What I would like to do is allow the user to type/select the path and file name so that I can then calculate the reference like so:

=Path&FileName&Sheet&”!$D$11″

i.e. I know where in the spreadsheet I want to get information from, I would just like to allow the user to select the worksheet they want to get the data from. The cell co-ordinates/range name will not change. Just the workbook name/location.

Can this be done without resorting to VBA?

Regards
Peter

Reply | Quote

Replies

I don’t know any way to do this without VBA. However, you can do something like this in VBA:

Since you know the path and name of the workbook, open the workbook and make it the active workbook. Then you can use code like the code below to allow the user to select any cell on the desired sheet, and the code will put the name of the sheet in the variable strSheet.

Dim oCell As Range
Dim strSheet As String
    On Error Resume Next
    Set oCell = Application.InputBox(prompt:="Select cell on desired sheet.", Type:=8)
    On Error GoTo 0
    If oCell Is Nothing Then
        MsgBox "No worksheet was selected."
        Exit Sub
    End If
    strSheet = oCell.Parent.Name

Reply | Quote

Thanks Legare

We type at crossed purposes.. Although I mentioned only a single cell in my post, I actually want to pull through several hundred cells. Basically I have a large model that I ‘think’ I could reduce in size by pulling out the reporting side of it. What I need to do then is to look back into a planning/calculation model to pull out some financial data which is presented in a variety of report formats. What I was hoping to do was to allow the user to select which ‘model’ they wanted to get their data from. This would allow the reporting tool to be generic rather than tied to a single model.

VBA may be the only way to achieve this. Thanks for your advice for now.

Regards
Peter

Reply | Quote

You could use INDIRECT function,(to NOT use VB), but the files referenced by INDIRECT must all be open!

Steve

Reply | Quote

You said that you knew the name of the workbook and the range of cells. The only thing that you didn’t know was the worksheet the user wanted to use. My code was just to give you the worksheet name, you can ignore the cell that the user clicked on – that is only necessary to make the InputBox method work. Once you have the worksheet name, you can use it with any range of cells that suits your fancy. If you tell us what the range is, and what you want to do with it, I can expand the code.

Reply | Quote