INDIRECT Link with another worksheet cell value

Re: INDIRECT Link with another worksheet cell value

I cannot test as network path but as indirect takes a string for local would be
=INDIRECT(CONCATENATE("[FGSummary_PossibleToRes_",C1,".xlsx]NResSoak!$J$1"))

So without testing try
=INDIRECT(CONCATENATE("\\192.168.9913\SSRS_DailySummaryReport\[FGSummary_PossibleToRes_",C1,".xlsx]NResSoak!$J$1"))

Re: INDIRECT Link with another worksheet cell value

Note: INDIRECT() does not work when referencing closed source files. If you have to reference closed files, you will need VBA... here is a popular UDF by Harlan Grove to do that: http://numbermonger.com/2012/0…inks-to-closed-workbooks/

There is an updated version half way down this thread.. updated 05/30/2004... not sure if there are still later versions... https://groups.google.com/foru…eet.functions/l9ObQ9ku6Bk

Re: INDIRECT Link with another worksheet cell value

I did neglect to mention that fact. Interesting reference NBVC

Re: INDIRECT Link with another worksheet cell value

The #REF error is because you are referencing a closed workbook. Anthony's formula won't work on a closed workbook.

Also, you should include the single quote to ensure the sheetname doesn't cause the problem:

=INDIRECT(CONCATENATE("[COLOR="#FF0000"]'[/COLOR][FGSummary_PossibleToRes_",C1,".xlsx]NResSoak[COLOR="#FF0000"]'[/COLOR]!$J$1"))

also, if the date is a real date formatted as you show, then you will need the TEXT function to hardcode the date...

e.g.

=INDIRECT(CONCATENATE("[COLOR="#FF0000"]'[/COLOR][FGSummary_PossibleToRes_",[COLOR="#FF0000"]TEXT(C1,"YYYYMMDD")[/COLOR],".xlsx]NResSoak[COLOR="#FF0000"]'[/COLOR]!$J$1"))

Re: INDIRECT Link with another worksheet cell value

You would need an Event macro that activates upon workbook opening.

While you have the FGSummar.xlsx open, hold the ALT key and press F11. This should open the VB editor. If you don't have a left pane then go to View|Project Explorer.

Look for your workbook name within parenthesis.... below that find and double click "ThisWorkbook"

paste this code in the VB Editor:

Private Sub Workbook_Open()
    Dim wbTo   As Workbook
    Dim DateText As String
    DateText = Format(Sheets("Sheet1").Range("C1").Value, "yyyymmdd")
    Set wbTo = Workbooks.Open("\\192.168.9913\SSRS_DailySummaryReport\[FGSummary_PossibleToRes_" & DateText &".xlsx")
    ThisWorkbook.Activate
End Sub

edit sheetname Sheet1, etc, as required.

Then save the workbook (you will need to save as .xlsm). The next time you open it and activate macros, it should auto-open the workbook.