• Calculate External References (Excel 97-SR2)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Calculate External References (Excel 97-SR2)

    Author
    Topic
    #378388

    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

    Viewing 0 reply threads
    Author
    Replies
    • #626115

      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
      
      • #626130

        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

        • #626155

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

          Steve

        • #626229

          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.

    Viewing 0 reply threads
    Reply To: Calculate External References (Excel 97-SR2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: