Forum Discussion

KennyK360's avatar
KennyK360
Copper Contributor
Jul 17, 2023

Searching multiple worksheets for different numbers simultaneously in Excel

Is it possible to search multiple worksheets for different numbers simultaneously in Excel?  Using CTL-F isn't working for me.    For example, can I search 10 different worksheets all at once for t...
excel
peiyezhu's avatar
peiyezhu
Bronze Contributor
Jul 17, 2023
My suggestion is to consolidate all sheets to one table and the search by sql.
  • KennyK360's avatar
    KennyK360
    Copper Contributor
    Jul 17, 2023
    Could I use Microsoft Access instead?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 18, 2023

      KennyK360 

      If output is entire row in the range for which criteria is met, as variant that could be like

      =LET(
    criteria, {137;142;154},
    k, SEQUENCE(, ROWS(criteria), , 0),
    combine, VSTACK(Sheet1:Sheet3!$A$1:$Z$1500),
FILTER(
    combine,
    BYROW(
        combine,
        LAMBDA(v,
            SUM( MMULT( k, --(v = criteria) ) )
        )
    )
))

      assuming you are on 365

      • KennyK360's avatar
        KennyK360
        Copper Contributor
        Jul 18, 2023

        The formula I am using is:
        =LET(Num,C1,All,SEQUENCE(MIN(Num-21,1479), , 21),Factors,FILTER(All,MOD(Num,All)=0),TRANSPOSE(Factors))

         

        I'm trying to edit the formula to automatically delete all rows with #CALC! (in this case 8, 9 and 10)

        Hopefully, this helps explain my problem. Thanks!

         

Resources