Working across several workbooks with multiple sheets can be tedious. Furthermore, file names are dynamic and can be changed by your users. Use this simple trick to organize your data and label workbook details.
Syntax
=CELL("filename", reference which is existed in same worksheet with the function)
Steps
- Type =CELL(
- Select or type range reference that includes the text "filename",
- Select or type dummy range reference A1
- Type ) to close function and press Enter to complete formula
Note: Although 2nd argument of the CELL function is optional, we advise to put a dummy reference which is at the same sheet with the CELL function itself. The reason behind this advice is that the CELL function returned info from active sheet even from different workbook.
How
The CELL function is a special function that can return information about worksheets or cells themselves rather than regular calculations. It has 2 arguments that first one is the information type and second one is the reference itself.
The "filename" type make the CELL function to return Full path[Workbook name]Worksheet name
=CELL("filename",A1)
Even second reference argument is optional, it is better to fill it with cell reference you want to get information about. The CELL function is a volatile function which is calculated every time that Excel is recalculated regardless of its precedents. So, it is important to make sure that the function addresses the right reference.
