Links within the workbook

[Nikhil Patki]

Why links break which are applied within same workbook
when we change the filename or destination

What can be done to avoid this kind of link breaks
How to make it get auto updated.

 

[Lutfor Rahman Shimanto]

Why links break which are applied within same workbook
when we change the filename or destination

What can be done to avoid this kind of link breaks
How to make it get auto updated.

Hello Nikhil Patki

Thanks for sharing your queries. If we create links within a workbook in Excel, the links are generated based on the file location and name. When you change the workbook's filename or destination, the links will break because the original file path or sheet name no longer corresponds to the modified one.

To avoid this situation, you should consider using relative references when linking.
Description of workbook link management and storage in Excel

I hope the resources will help you with your problems.

Regards
Lutfor Rahman Shimanto

 

[Nikhil Patki]

actually i have a sheet in which there are almost of dozens of columns regarding employee details.
in second sheet i only want to copy specific columns details as well as only active employee names.
also in second sheet besides these details there is attendance part being placed.
hence i am unable to use advance filter to extract data on second sheet.
For the reason I've used advance filter option on first sheet itself
and then that range I've linked on second sheet
so as the necessary data only appears without hassle.

 

[Lutfor Rahman Shimanto]

actually i have a sheet in which there are almost of dozens of columns regarding employee details.
in second sheet i only want to copy specific columns details as well as only active employee names.
also in second sheet besides these details there is attendance part being placed.
hence i am unable to use advance filter to extract data on second sheet.
For the reason I've used advance filter option on first sheet itself
and then that range I've linked on second sheet
so as the necessary data only appears without hassle.

Hello Nikhil Patki

I understand the complexity of your workbook structure with multiple columns of employee details and attendance records and the need to extract specific information from another sheet.

One practical approach is to use relative references when creating links to address the issue of link breaks when changing filenames or destinations. Relative references are more flexible and can adapt to changes in file paths or locations within the workbook. This helps ensure that your links remain intact even when you modify the file.

You can use Excel's functions like VLOOKUP, INDEX-MATCH, or even the newer dynamic array functions such as FILTER to retrieve and display the required information on the second sheet. These functions allow you to create dynamic connections between sheets without explicitly linking.

Regards
Lutfor Rahman Shimanto