EXCEL

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.