Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique within group (2002 SP3)

    In the attached sheet, as part of a selection formula, I use AND(OR(LEFT(H4,2)="Ra",LEFT(H4,2)="Ch"),COUNTIF(OF FSET(F4,0,0,-ROW()+1,1),F4)=1 to identify the first occurrence of a Radio Button or Check Box on a form. This works fine if the data in the sheet is from only one form. However, if there are multiple forms in the sheet, the formula causes a control of the same name to not appear when the list is filtered for subsequent forms.

    So, in the attached sheet, the radio button named "subrate" should appear (once) when the list is filtered for column I = TRUE and column B either 19 or 20.

    Any ideas how I can make my formula return TRUE for the first (or last) occurrence of any particular named one of these elements within a form?

    Hope this makes sense!

    TIA
    Regards
    Paul
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique within group (2002 SP3)

    Does this do what you want (in I2, fill down as far as needed)?

    =OR(LEFT(H2,2)="Te",SUMPRODUCT(OR(LEFT(H2,2)="Ch", LEFT(H2,2)="Ra")*((LEFT(H$2:H2,2)="Ch")+(LEFT(H$2: H2,2)="Ra"))*(B$2:B2=B2))=1,LEFT(H2,2)="Se")

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique within group (2002 SP3)

    Doesn't seem to in this extended version.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique within group (2002 SP3)

    In which cell or cells does the formula return a different result than you intended?

  5. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique within group (2002 SP3)

    In all of the subrate ones. It is showing FALSE on all of them :-(

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique within group (2002 SP3)

    Sorry, I had assumed that the formula from your first post worked correctly if you don't take the form (column [img]/forums/images/smilies/cool.gif[/img] into account, I omitted to check whether it actually did (it doesn't).

    Does this work better?

    =OR(LEFT(H2,2)="Te",SUMPRODUCT(OR(LEFT(H2,2)="Ch", LEFT(H2,2)="Ra")*($F$2:F2=F2)*(B$2:B2=B2))=1,LEFT( H2,2)="Se")

  7. #7
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique within group (2002 SP3)

    Wow Hans. Not only does it work, it is better than my original formula (which did appear to work OK when there was only one form present).

    Thank you, Thank you, Thank you, Thank you.

Posting Permissions

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