Modify formula with indirect function

Question

Modify formula with indirect function

Chaturvedi, Santosh 450

All data are not real as shown below..

In below formula:

=LET(

Data, 'file:///Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\3_Fatigue Assesment_as per 17949_Welded Joints\Fatigue Assesment_Collective_1[Fatigue Assesment_Welded Joints_C1_RSL31.xlsm]C1_RSL31'!$E$2473:$F$3573,

SORT(FILTER(Data, BYROW(Data, LAMBDA(r, SUM(r)>0)), "N"), 1, -1)

The address :

'file:///Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\3_Fatigue Assesment_as per 17949_Welded Joints\Fatigue Assesment_Collective_1[Fatigue Assesment_Welded Joints_C1_RSL31.xlsm]C1_RSL31'

Is in sheet "Dash Board_Data_File" in cell H13

How to modify the above let function using indirect function

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Store the external reference text in H13, then build the range text and pass it to INDIRECT.

Use:

=LET(
    RefText, "'" & 'Dash Board_Data_File'!H13 & "'!$E$2473:$F$3573",
    Data, INDIRECT(RefText),
    SORT(FILTER(Data, BYROW(Data, LAMBDA(r, SUM(r)>0)), "N"), 1, -1)
)

If H13 already contains the workbook and sheet portion like:

file:///Q:\...\[Fatigue Assesment_Welded Joints_C1_RSL31.xlsm]C1_RSL31

this formula turns it into a text reference such as:

'file:///Q:\...\[Fatigue Assesment_Welded Joints_C1_RSL31.xlsm]C1_RSL31'!$E$2473:$F$3573

Important points:

INDIRECT requires a valid text reference.
If ref_text refers to another workbook, that workbook must be open, otherwise INDIRECT returns #REF!.
External references are not supported in Excel for the web.
If the path/sheet text in H13 contains spaces, keeping the single quotes around the reference is correct.

If the full external reference including the cell range is already in H13, use:

=LET(
    Data, INDIRECT('Dash Board_Data_File'!H13),
    SORT(FILTER(Data, BYROW(Data, LAMBDA(r, SUM(r)>0)), "N"), 1, -1)
)

References: