Need to fill"-" after the end of linked entry.

Good day @Chaturvedi, Santosh,

To automatically fill the remaining rows in columns C, D, and E with “-” after the linked data ends, you can replace your current formula in cell C7 with the following:

=LET( 
    d, '[Preprocessing_Final_MACROS_150 sheets_macros.xlsm]S1'!C7#, 
    targetRows, 700-ROW(C7)+1, 
    pad, MAX(targetRows-ROWS(d),0), 
    IF( 
        pad>0, 
        VSTACK(d, IF(SEQUENCE(pad, COLUMNS(d))>0, "-", "")), 
        d 
    ) 
)

This formula will first return all the values from your linked range, and once the data ends, it will automatically continue filling “-” down to row 700. You can adjust the number 700 to match the last row you want your table to reach.

Note:

• Please enter the formula only in the top cell C7 and leave the cells below empty so the formula can spill naturally. If anything is typed into the cells underneath, Excel will not be able to expand the result and you may run into a SPILL error.
• This solution uses newer functions like LET, VSTACK, and SEQUENCE, so it will work in Microsoft 365. On older Excel versions, a NAME error is expected because those functions are not available there.
• Also, since the formula is pulling data from an external workbook, it is best to keep the source file open while working. This helps ensure the reference to C7# is resolved correctly and avoids unexpected issues.

Hope this helps smooth things out for you. If you’d like to adjust it to better fit your table size or if anything changes on your side, please feel free to share an update and I’ll be happy to assist further.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.