link with two sheets in differnt work books

Answerts:

• Are you trying to create a link in the Target File that opens the Source File directly to the S2 worksheet? Yes this is correct

Could you please confirm whether you would prefer to use a formula, hyperlink, or VBA solution for this requirement? This will help us provide the most suitable recommendation.

I am using following formula in target file. - It goes to source file by S2 (see below)

=LET(

d, 'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Duplicating_150 sheets_Macros\[Preprocessing_Final_MACROS_150 sheets_macros.xlsm**]S2'!C7#**,

targetRows, 600-ROW(C7)+7,

pad, MAX(targetRows-ROWS(d),0),

IF(

    pad>0,

    VSTACK(d, IF(SEQUENCE(pad, COLUMNS(d))>0, "-", "")),

    d

)

)

Is the goal for the formula to retrieve data from the Source File and populate the Target File, or is the primary purpose to navigate to the referenced worksheet?

Copy the data from the souse file (this is correctly happening) and also shows that the sorce file with correct TAB (here is "S2" Souce file")

I do have many sheet in Source file..S2, S3, S4...150.

This means when i work in target file in "S2 tab target file" and open the source file (via "edit link".) the correct sheet (here is "S2 source file" must open and visible amoung others sheets tabs ranging from S3.4..5..6....150

The data is correctly linked between teh target file and source file

But he sheet is not shown active in source file rather it showing other sheet that is not relevant in the formula as shown above.

1. The Formula Bar showing the complete external reference formula. As shown above
2. The worksheet tabs visible in the Source File after it opens. it shows the first tab not teh linked tab "S2".
3. What happens when you click the link, including any dialog boxes, messages, or navigation behavior that appears. When i open via edit link --> open sorce--> all open cooectly.

Hi @Chaturvedi, Santosh,

Thank you for the additional details and clarification. 

Based on your description, it appears that the data link itself is working correctly. The formula successfully retrieves data from the S2 worksheet in the source workbook, and the source workbook opens without any errors. 

However, I would like to reconfirm whether the feature you are referring to is the Workbook Links > Open workbook option shown below: 

If so, please note that the Open workbook command is designed to open the referenced source workbook only. It does not navigate to or activate the specific worksheet referenced by the formula. 

In your example, although the formula references: 

[Preprocessing_Final_MACROS_150 sheets_macros.xlsm]S2!C7#

the Open workbook action opens the source workbook, but Excel typically displays whichever worksheet was active when the workbook was last saved (or another default worksheet determined by the workbook state). It does not automatically switch to the S2 worksheet based on the formula reference. 

Therefore, the behavior you are seeing is expected: 

•  The formula correctly retrieves data from S2 in the source workbook. 
•  The source workbook opens successfully. 
•  The S2 worksheet is not automatically activated when opening the workbook through Workbook Links > Open workbook. 

To the best of my knowledge, there is currently no built-in Excel feature that causes the Open workbook option in the Workbook Links pane to open the source workbook directly to the worksheet referenced by an external formula. 

Thank you for your time and understanding. I look forward to your response. 

Ok Henry -- I do understand the explnation. Thanks for providing teh same. Have a nice day !!