1

I am using a formula for Data Validation to hide used names like this: =IF(COUNTIF(DrawUsed, [Players])>=1, "", [Players]), where DrawUsed is a named range. The problem is that my table looks like this:

enter image description here

I need the Blind Draw Team columns to be the first parameter of the COUNTIF, since if a name is used in the first column, it should not be available for the third Blind Draw Team column. The issue is that this formula, with a named range defined =Blind!$B$2:$B$27,Blind!$I$2:$I$27,Blind!$P$2:$P$27 results in nothing but #VALUE! errors.

I cannot define a contiguous range because the Buyer columns should not be dependent on the values of Blind Draw Team, and should not hide used names, either.

Does anyone have any advice?

1 Answer 1

2

I solved this by defining 3 separate ranges and this formula:

=IF(
    OR(
        COUNTIF(BlindUsed1, [Players])>=1,
        COUNTIF(BlindUsed2, [Players])>=1,
        COUNTIF(BlindUsed3, [Players])>=1
    ),
    "", 
    [Players]
)

Returns for legibility's sake.

Update

Solution 2: INDIRECT can handle non-contiguous ranges. I had a range that was literally just a bunch of noncontiguous cells. I defined it like this:

=INDIRECT(
    {
        "Sunday!$F$7",
        "Sunday!$F$15",
        "Sunday!$F$23",
        "Sunday!$F$31",
        "Sunday!$F$39",
        "Sunday!$M$7",
        "Sunday!$M$15",
        "Sunday!$M$23",
        "Sunday!$M$31",
        "Sunday!$M$39",
        "Sunday!$T$7",
        "Sunday!$T$15",
        "Sunday!$T$23",
        "Sunday!$T$31",
        "Sunday!$T$39"
    }
)

Then COUNTIF works on the range: =COUNTIF(SundayCaptains,[Player]). The only con with this method is the values won't update if you rename the worksheet.

2
  • Just curious, did you format that here, or have some program/software that "exploded" it out for you? I like it!
    – BruceWayne
    Nov 5, 2020 at 18:24
  • 1
    @BruceWayne I formatted it here. Excel formulas can be so hard to read, lol.
    – mas
    Nov 5, 2020 at 19:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .