Forum Discussion
Dynamic hyperlink
The CONCAT function in Excel is used for concatenating (joining) multiple text strings into one. It cannot be directly used to extract specific data from a closed workbook.
use the INDIRECT function with a closed workbook, you need to provide the full file path along with the workbook name. Excel has a feature called "external references" that allows you to refer to cells in closed workbooks.Here's an example of how you can modify the formula in column B to reference a closed workbook:
Assuming you have the workbook names listed in column A starting from cell A2, and you want to link cell A1 from each workbook to the corresponding cell in column B:
In cell B2, enter the following formula: =INDIRECT("'C:/folder/" & A2 & ".xlsm'!A1", TRUE)
This formula uses the INDIRECT function with the external reference flag (TRUE) to refer to cell A1 in the "Bob.xlsm" workbook located in the "C:/folder/" directory, even when the workbook is closed.Drag the formula down to populate the formula in the rest of the cells in column B for each person.
Now, whether the "Bob.xlsm" workbook is open or closed, the corresponding cell in column B will display the value from cell A1 in the "Bob.xlsm" workbook located in the "C:/folder/" directory.
Please adjust the folder path and file extension in the formula to match your specific setup.
Note: Using external references to closed workbooks may cause the calculation performance to be slower, especially when you have a large number of formulas referencing closed workbooks. It is recommended to use this approach sparingly and only when necessary.
The text was created with the help of AI.
Thanks, NikolinoDE That is more or less what I had in mind, but I would like to go one step further. Can the cell be updated instead of just creating the link as string? For example:
Sheet1.xlsm
| email adress | age | |
| Bob | ||
| Jack |
Workbook Bob is located in c:/folder/Bob.xlsm
I can manually connect the A1 cell from Bob.xlsm to B2 in sheet1.xlsm, the cells will sync.
My idea is to make the formula in Column B connect to the File in c:/folder depending on the name on column A, this way, if I create a new file in the folder (using the same pattern of naming) I could just insert the name in Column A. Does that make any sense? Can CONCAT produce a function command? Maybe I will need a macro for this, I did it on Google Sheets once, but excel doesn't seem to produce the same results.
The CONCAT function in Excel is used for concatenating (joining) multiple text strings into one. It cannot be directly used to extract specific data from a closed workbook.
use the INDIRECT function with a closed workbook, you need to provide the full file path along with the workbook name. Excel has a feature called "external references" that allows you to refer to cells in closed workbooks.
Here's an example of how you can modify the formula in column B to reference a closed workbook:
Assuming you have the workbook names listed in column A starting from cell A2, and you want to link cell A1 from each workbook to the corresponding cell in column B:
In cell B2, enter the following formula: =INDIRECT("'C:/folder/" & A2 & ".xlsm'!A1", TRUE)
This formula uses the INDIRECT function with the external reference flag (TRUE) to refer to cell A1 in the "Bob.xlsm" workbook located in the "C:/folder/" directory, even when the workbook is closed.
Drag the formula down to populate the formula in the rest of the cells in column B for each person.
Now, whether the "Bob.xlsm" workbook is open or closed, the corresponding cell in column B will display the value from cell A1 in the "Bob.xlsm" workbook located in the "C:/folder/" directory.
Please adjust the folder path and file extension in the formula to match your specific setup.
Note: Using external references to closed workbooks may cause the calculation performance to be slower, especially when you have a large number of formulas referencing closed workbooks. It is recommended to use this approach sparingly and only when necessary.
The text was created with the help of AI.
- That worked! Thank you so much!