Referencing a Static Workbook : Dynamic Worksheet - not using INDIRECT

[spydey]

I have two workbooks: A & B

In workbook A worksheet 1, cell A1 has a date. I input it as: mm/dd/yyyy, but it visually shows as: Month Year.

Example: Inputting 04/01/2019 will visually show as: April 2019.

In workbook B, I have multiple worksheets. Each sheet is labeled after a Month and Year

Example: April 2019

I need a formula in Workbook A that will go to workbook B, the correct sheet in workbook b based upon the month and year from the date of workbook A sheet 1 cell A1, and return a value from a specified cell within that worksheet in workbook B.

I know I can use INDIRECT. I have used it for more simpler things, so I would need to goof around with it to get it to work in this case. But my concern is that I would need to have both workbooks open for INDIRECT to work, otherwise I get a #REF error.

I don't want to have to have workbook B open.

Is there a way I can do it without the need to open workbook B? I guess, can I do it without using INDIRECT?


-Spydey

 

[Tom Urtis]

Assuming...
• It is cell F7 in the sheet of interest in workbook B whose value you want returned, and
• It is cell B5 in the active sheet of workbook A that you want said value to be located, and
• It is a formula that you want in cell B5 of the active sheet to show the value in cell F7 of said sheet then

This macro does what you want.
Modify for folder path, cell locations, and edit for Value instead of Formula if you just want the static constant value to be returned instead of a paste link formula.

Works for me when tested while workbook B was closed, further assuming there really is a worksheet named in workbook B to match the MMMM YYYY formatted value of the date in cell A1 of Sheet1 of workbook A.

Sub Test1()
Dim strDate$, strPath$
strPath = "C:\Your\File\Path\[WorkbookB.xlsm]"
strDate = Sheets("Sheet1").Range("A1").Text
Range("B5").Formula = "=" & "'" & strPath & strDate & "'!F7"
End Sub

 

[spydey]

Assuming...
• It is cell F7 in the sheet of interest in workbook B whose value you want returned, and
• It is cell B5 in the active sheet of workbook A that you want said value to be located, and
• It is a formula that you want in cell B5 of the active sheet to show the value in cell F7 of said sheet then

This macro does what you want.
Modify for folder path, cell locations, and edit for Value instead of Formula if you just want the static constant value to be returned instead of a paste link formula.

Works for me when tested while workbook B was closed, further assuming there really is a worksheet named in workbook B to match the MMMM YYYY formatted value of the date in cell A1 of Sheet1 of workbook A.

Sub Test1()
Dim strDate$, strPath$
strPath = "C:\Your\File\Path\[WorkbookB.xlsm]"
strDate = Sheets("Sheet1").Range("A1").Text
Range("B5").Formula = "=" & "'" & strPath & strDate & "'!F7"
End Sub

Awesome code there @Tom Urtis.

Thanks for the assistance.

-Spydey