Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate External References (Excel 97-SR2)

    Is there any way I can 'calculate' external references?

    ='nath1path2path3Excel[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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate External References (Excel 97-SR2)

    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.

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate External References (Excel 97-SR2)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculate External References (Excel 97-SR2)

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

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate External References (Excel 97-SR2)

    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.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •