Super User Asked by malan88 on December 30, 2020
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:
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?
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.
Answered by malan88 on December 30, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP