how to make adresse book in excel sheet to control the formula present in many sheets having filter formula buit init.

Chaturvedi, Santosh 430 Reputation points
2026-09-20T11:17:18.24+00:00

I have one situation

  1. I have two folders folder_1 and folder_2,
  2. Folder 1 ([DB_Data_Collectives_2.xlsm]) have one excel sheet_1 : S1
  3. Folder 2 have differnt excel sheet_2
  4. Excel 2 have Filter forrmula in one cell as below =FILTER('Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints[DB_Data_Collectives_2.xlsm]S1'!$C$6:$DB$105, (BYROW('Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints[DB_Data_Collectives_2.xlsm]S1'!$G$6:$DB$105, LAMBDA(r, SUM(r)>0))),"N")
  5. I want to make one "addresee book excel sheet" where [DB_Data_Collectives_2.xlsm]S1 will be changed to [DB_Data_Collectives_2.xlsm]S2, [DB_Data_Collectives_2.xlsm]S3, [DB_Data_Collectives_2.xlsm]S4, and so one.
  6. I want to control the "addresee book excel sheet" so that the formula in step 4 cahnges and takes the values from different sheet say S1,S2,S3,S4 etc.

Please advice how to make the addresee book and link the addresse to the step 4 formulas.

Microsoft 365 and Office | Excel | For business | Other
0 comments

2 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Marcin Policht 108.3K Reputation points MVP Volunteer Moderator
    2026-09-20T13:18:41.71+00:00

    Yep - you can create an "Address Book" sheet in Folder 2 that contains the source sheet names and then make the FILTER formula dynamically use the selected sheet name. Use one cell that controls which source sheet is used.

    Create an Excel sheet called AddressBook. Set it up with the following columns:

    No. | Source Sheet | Description
1 | S1 | Fatigue Data - S1
2 | S2 | Fatigue Data - S2
3 | S3 | Fatigue Data - S3
4 | S4 | Fatigue Data - S4

    You can continue adding S5, S6, S7, etc. as required.

    Then create a control cell, for example AddressBook!E2, where you select the required source sheet. You can make E2 a drop-down list containing S1, S2, S3, S4, etc.

    Your existing formula currently has S1 hard-coded into the external reference. Instead, the formula can take the sheet name from AddressBook!E2.

    Use this formula:

    =LET( SheetName,AddressBook!E2, SourceRange,INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints[DB_Data_Collectives_2.xlsm]"&SheetName&"'!$C$6:$DB$105"), CheckRange,INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints[DB_Data_Collectives_2.xlsm]"&SheetName&"'!$G$6:$DB$105"), FILTER(SourceRange,BYROW(CheckRange,LAMBDA(r,SUM(r)>0)),"N") )

    With this arrangement, if AddressBook!E2 contains S1, the formula reads from S1. If you change E2 to S2, it automatically reads from S2. Changing it to S3 reads from S3, and so on. The FILTER formula itself does not need to be changed.

    You can also make the Address Book use a numeric selection instead. For example, E2 could contain 1, 2, 3 or 4, while the Source Sheet column contains S1, S2, S3 and S4. In that case, the formula can retrieve the sheet name using XLOOKUP:

    =LET( SheetName,XLOOKUP(AddressBook!E2,AddressBook!A:A,AddressBook!B:B), SourceRange,INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints[DB_Data_Collectives_2.xlsm]"&SheetName&"'!$C$6:$DB$105"), CheckRange,INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints[DB_Data_Collectives_2.xlsm]"&SheetName&"'!$G$6:$DB$105"), FILTER(SourceRange,BYROW(CheckRange,LAMBDA(r,SUM(r)>0)),"N") )

    This second approach should work better if your Address Book is to become a larger control table because the user selects a number and the Address Book determines the corresponding worksheet.

    Note that INDIRECT has a limitation with external workbooks - the referenced DB_Data_Collectives_2.xlsm workbook generally needs to be open for INDIRECT to resolve the dynamically constructed reference.

    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

    Was this answer helpful?

  2. AI answer

    2026-09-20T11:17:37.84+00:00

    Use a cell-driven sheet reference, so the source sheet name is controlled from one place.

    In the “address book” workbook or control sheet:

    1. Put the source sheet name in one cell, for example A1
    2. Enter values like S1, S2, S3, S4 in that cell
    3. Build the reference from that cell instead of hard-coding S1

    The Excel pattern for using a sheet name stored in a cell is:

    =INDIRECT("'"&A1&"'!A22")

    For the current scenario, the same idea applies: replace the fixed sheet name in the external reference with text built from the control cell.

    Example structure:

    =INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\[DB_Data_Collectives_2.xlsm]"&A1&"'!C6:DB105")

    and for the BYROW range:

    =INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\[DB_Data_Collectives_2.xlsm]"&A1&"'!G6:DB105")

    Then the FILTER formula becomes this pattern:

    =FILTER(
INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\[DB_Data_Collectives_2.xlsm]"&A1&"'!C6:DB105"),
BYROW(
INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\[DB_Data_Collectives_2.xlsm]"&A1&"'!G6:DB105"),
LAMBDA(r,SUM(r)>0)
),
"N"
)

    How to set up the address book sheet:

    1. Create a sheet, for example Control
    2. In A1, store the active source sheet name: S1
    3. Optionally list allowed sheet names below it:
      • A2 = S1
      • A3 = S2
      • A4 = S3
      • A5 = S4
    4. In the workbook that contains the FILTER formula, change A1 to the required sheet name whenever the source must switch

    If the control cell is on a sheet named Control, use Control!A1 instead of A1:

    =FILTER(
INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\[DB_Data_Collectives_2.xlsm]"&Control!A1&"'!C6:DB105"),
BYROW(
INDIRECT("'Q:\Presentations\PE_31\O_Analysis and calculation\Fatigue\Backups_Marcros\Fatigue Assesment_as per 17949_Welded Joints\[DB_Data_Collectives_2.xlsm]"&Control!A1&"'!G6:DB105"),
LAMBDA(r,SUM(r)>0)
),
"N"
)

    If only the workbook link itself needs to be created or changed manually, Excel also supports creating workbook links by typing = and selecting the source workbook and sheet, or by using Paste Link.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.