Forum Discussion

WittyGuy55's avatar
WittyGuy55
Copper Contributor
Nov 15, 2023
Solved

EXCEL

I have two EXCEL files named YEAR 2023.xlsm and YEAR 2022.xlsm, and let’s assume that each file has its year number in cell A1. I want to bring a number from the 2022 file into the 2023 file.  But I ...
excel
  • NikolinoDE's avatar
    NikolinoDE
    Nov 15, 2023

    WittyGuy55 

    To dynamically reference the previous year's file in Excel, you can use a combination of text functions and the INDIRECT function. Assuming that you want to bring in a number from cell A5 in the previous year's file to the current year's file, you can use the following formula:

    =INDIRECT("'YEAR " & (VALUE(MID(CELL("filename",A1),FIND("YEAR ",CELL("filename",A1))+5,4))-1) & ".xlsm'!A5")

    Here's a breakdown of the formula:

    1. CELL("filename", A1): This function returns the full path and filename of the current workbook.
    2. FIND("YEAR ", CELL("filename", A1)) + 5: This part finds the position of "YEAR " in the filename and adds 5 to get the starting position of the year.
    3. MID(...): This extracts the year from the filename.
    4. VALUE(...) - 1: This converts the extracted year to a number and subtracts 1 to get the previous year.
    5. "'YEAR " & ... & ".xlsm'!A5": This constructs the text string with the filename of the previous year's workbook and the cell reference A5.
    6. INDIRECT(...): This function converts the constructed text string into a valid reference and retrieves the value from the specified cell in the previous year's file.

    Please replace A5 with the actual cell reference you want to reference in the previous year's file.

    Note: The INDIRECT function is a volatile function, meaning it recalculates every time there is a change in the worksheet. Using too many volatile functions can impact performance in large workbooks. If you have a large dataset or multiple formulas like this, consider other methods or updating the references manually if the data doesn't change frequently. The text was revised with the AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 15, 2023

WittyGuy55 

To dynamically reference the previous year's file in Excel, you can use a combination of text functions and the INDIRECT function. Assuming that you want to bring in a number from cell A5 in the previous year's file to the current year's file, you can use the following formula:

=INDIRECT("'YEAR " & (VALUE(MID(CELL("filename",A1),FIND("YEAR ",CELL("filename",A1))+5,4))-1) & ".xlsm'!A5")

Here's a breakdown of the formula:

  1. CELL("filename", A1): This function returns the full path and filename of the current workbook.
  2. FIND("YEAR ", CELL("filename", A1)) + 5: This part finds the position of "YEAR " in the filename and adds 5 to get the starting position of the year.
  3. MID(...): This extracts the year from the filename.
  4. VALUE(...) - 1: This converts the extracted year to a number and subtracts 1 to get the previous year.
  5. "'YEAR " & ... & ".xlsm'!A5": This constructs the text string with the filename of the previous year's workbook and the cell reference A5.
  6. INDIRECT(...): This function converts the constructed text string into a valid reference and retrieves the value from the specified cell in the previous year's file.

Please replace A5 with the actual cell reference you want to reference in the previous year's file.

Note: The INDIRECT function is a volatile function, meaning it recalculates every time there is a change in the worksheet. Using too many volatile functions can impact performance in large workbooks. If you have a large dataset or multiple formulas like this, consider other methods or updating the references manually if the data doesn't change frequently. The text was revised with the AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

WittyGuy55's avatar
WittyGuy55
Copper Contributor
Nov 15, 2023
Of course, this solution involving the INDIRECT function doesn't work unless the "from" file is open. Is there a way around this difficulty?
  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 16, 2023

    WittyGuy55 

    When working with closed workbooks, you'll face limitations due to security restrictions in Excel.

    If your use case involves regularly pulling data from closed workbooks, you might want to consider using Power Query or Power Pivot, which are more advanced features in Excel designed for data manipulation and analysis. These tools allow you to connect to external data sources, including closed workbooks, and create more complex data models.

    Alternatively, you could use VBA (Visual Basic for Applications) to automate the process of opening and closing the workbook to retrieve the data. VBA allows for more flexibility and control in such scenarios.

    Here's a simple example of VBA code that you could use in Excel:

    Function GetDataFromClosedWorkbook(filePath As String, sheetName As String, cellAddress As String) As Variant
    Dim closedWorkbook As Workbook
    Set closedWorkbook = Workbooks.Open(filePath, ReadOnly:=True)
    
    GetDataFromClosedWorkbook = closedWorkbook.Sheets(sheetName).Range(cellAddress).Value
    
    closedWorkbook.Close False
End Function

    You would then call this function from a cell in your active workbook, like this:

    =GetDataFromClosedWorkbook("C:\Path\YEAR 2022.xlsm", "Sheet1", "A5")

    Remember to adjust the file path, sheet name, and cell address based on your specific setup.

    Keep in mind that using VBA requires enabling macros and may involve security considerations.

Resources