Forum Discussion

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

KennyK360

Copper Contributor

Jul 17, 2023

Could I use Microsoft Access instead?

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
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!
- SergeiBaklan
  Diamond Contributor
  Jul 19, 2023
  KennyK360
  
  Looks like totally different task. Other words, delete in the range entire rows which have cells with error in one or more columns, correct?
  
  But that can't be done with formula, VBA or ExcelScript.
  - KennyK360
    Copper Contributor
    Jul 19, 2023
    Yes, that is correct (delete in the range entire rows which have cells with error in one or more columns). It can't be done with formula, VBA or ExcelScript...are you saying there are no options to achieve this task in Excel? Thank you for your time.