Forum Discussion
Dynamic hyperlink
Hello! I searched and couldn't find exactly what I'm trying to do in other threads.
There is a folder with different workbooks for each person, let's say Bob.xlsm; Jack.xlsm....
I have another file in which I have a sheet for each one of them.
Since they are all in the same folder, the only thing that changes is the last part, I would like to make the link to the workbook dynamic, as in pull the name on the sheet, since the sheetname and the workbook name are the same. Since I'm gonna need to do it every year, I don't want to manually connect every sheet to every workbook, also, there are about 3 new people every month.
I hope I was clear enough. I am pretty amateur, I thought about using concat but I'm not sure it would work.
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.
To create dynamic hyperlinks in Excel that pull the workbook names from the sheet names, you can use a combination of the HYPERLINK function and concatenation.
Here is an example of how you can achieve this:
- In your Excel file, create a sheet for each person, with the sheet name matching the respective workbook name (e.g., "Bob", "Jack", etc.).
- In the sheet where you want to create the dynamic hyperlinks, enter the workbook names in a column. Let's assume you have the workbook names listed in column A starting from cell A2.
- In the adjacent column (e.g., column B), enter the following formula in the corresponding row (e.g., B2) to create the dynamic hyperlink:
=HYPERLINK(CONCATENATE("'[",A2,".xlsm]'",A2,"'!A1"),A2)
Please note that this method may not work in older versions of Excel. In such cases, you can use the ampersand (&) operator to concatenate the strings instead. Modified the formula as follows for older Excel versions: =HYPERLINK("[" & A2 & ".xlsm]" & A2 & "'!A1", A2)
This formula uses concatenation to construct the file path and sheet reference based on the workbook name in column A. It creates a hyperlink that links to cell A1 in the respective sheet.
- Drag the formula down to populate the formula in the rest of the cells in column B for each person.
Now, each cell in column B will contain a dynamic hyperlink that points to the respective workbook based on the sheet name. When you click on a cell with a hyperlink, it will open the corresponding workbook.
Make sure to adjust the column references and adjust the file extension (e.g., .xlsm) if your workbooks have a different extension.
This approach allows you to dynamically create hyperlinks based on the workbook names listed in your sheet, making it easier to add new people and workbooks without manually creating individual links for each one. The text and formulas was created by the AI.
Hope this will help you.
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.xlsmemail 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.
