Results 1 to 2 of 2
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Excel2007: Checking visible cells for a specified text prefix

    Hi

    I am using the following formula to check whether any of the visible cells in a particular column range start off with a specified text sequence. (Some rows may be hidden).

    For example,
    Code:
    =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1:C1000,ROW(C1:C1000)-ROW(C1),,1)),--(LEFT(C1:C1000,8)="Product:"))>0
    ..will check for any cells starting with "Product:" in the visible rows displayed in the row range [1:1000] in column [C]

    Is there a more efficient way or formula to do this????

    zeddy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The thing that comes to my mind is to multipy them to eliminate the 2 (--) actions. I also used 103 since you mention "hidden". 3 will only ignore hidden by filtering. The 103 will ignore hidden by filtering AND hidden by expliciting hiding the row:
    Code:
    =SUMPRODUCT(SUBTOTAL(103,OFFSET(C1:C1000,ROW(C1:C1000)-ROW(C1),,1))*(LEFT(C1:C1000,8)="Product:"))>0
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •