Refer the cell value of an excel sheet with anothe

Question

I have two excel worksheets, The first is:

Test.xlsx

and the second:

Sample.xlsx

I'm referring the B10 cell value in the Test.xlsx with the C10 of Sample.xlsx using the formula:

='C:\Users\Happy\Desktop\Work[Sample.xlsx]Sheet1'!C10 

in the B10 of Test.xlsx file.

Can anyone suggest me how to replace the hard coded path of the destination file in the formula:

("C:\Users\Happy\Desktop\Work")

with an environmental variable "Temp" with the value "C:\Users\Happy\Desktop\Work".

Thank you.

Answer 1

Let's assume your temp path is stored on cell E1. on B10 enter:

=INDIRECT(E1&"!C10")

Of course you can use the name Temp instead of E1 . Just name the E1 cell (or any other cell you desire) Temp and change the formula to :

=INDIRECT(Temp&"!C10")

• To name the cell click on it and change the name in the box on the left top side

Answer 2

You will need to use VBA to expand environment variables. If the folder is always on the user's desktop then something like this might suffice. Place this into a standard code module in your workbook:

Public Function GetEnviron(strEnv As String, Optional folderAppend As String = vbNullString) As String

    If Not folderAppend = vbNullString Then
        folderAppend = folderAppend & IIf(Right(folderAppend, 1) = "\", vbNullString, "\")
    End If

    GetEnviron = Environ(UCase(strEnv)) & "\" & folderAppend

End Function

And in your spreadsheet use like:

=INDIRECT("'"&GetEnviron("USERPROFILE","Desktop\Work\")&"[Sample.xlsx]Sheet1'!C10")

---

The Environ() function in VBA is used to expand environment variable strings - so for example:

• Environment variable: %USERPROFILE% | Returns: (C:\Users\[username])
  • VBA Environ("USERPROFILE") | Returns: (C:\Users\[username])
• Environment variable: %APPDATA% | Returns: (C:\users\[username]\AppData\Roaming)
  • VBA Environ("APPDATA") | Returns: (C:\users\[username]\AppData\Roaming)

Answer 3

I have 2 files: Book1.xlsx and Book2.xlsx Go to the Book2.xlsx and special cell and type "=[Book1.xlsx]Sheet1!$A$5" for example. When you change the value in Book1.xlsx.A5 and save, Book2.xlsx takes effect.