Forum Discussion
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 want that ‘2022’ in the filename to be a variable. By that, I mean next year’s file will be YEAR 2024.xlsm, and I’ll want to bring in a number from the YEAR 2023.xlsm file. What formula do I use to bring in a number, say from cell A5, in the previous year’s file to the current year’s file?
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:
- CELL("filename", A1): This function returns the full path and filename of the current workbook.
- 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.
- MID(...): This extracts the year from the filename.
- VALUE(...) - 1: This converts the extracted year to a number and subtracts 1 to get the previous year.
- "'YEAR " & ... & ".xlsm'!A5": This constructs the text string with the filename of the previous year's workbook and the cell reference A5.
- 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.
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:
- CELL("filename", A1): This function returns the full path and filename of the current workbook.
- 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.
- MID(...): This extracts the year from the filename.
- VALUE(...) - 1: This converts the extracted year to a number and subtracts 1 to get the previous year.
- "'YEAR " & ... & ".xlsm'!A5": This constructs the text string with the filename of the previous year's workbook and the cell reference A5.
- 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.
- Of course, this solution involving the INDIRECT function doesn't work unless the "from" file is open. Is there a way around this difficulty?
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 FunctionYou 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.
- Thanks Nikolino. I never would have found the solution on my own. The more I get into EXCEL, the more I learn how powerful it is, and the more I could learn about its features. I'd love to find a book about all these great features. Any recommendations ? Thanks again!
