Is there a way to find a value in a set of cells?
 

I am trying to find at least one occurence of a specific value in a set
of cells that are going to be scattered in a spreadsheet. Here is a
pseudocode example what I am trying to do:

If any value in
{a2, a15, b2, b22, c5, c7, c9, d45, d55, e1, e17}
= 1
Then perform this action
Else perform this action

The value could appear more than once, and I am testing for only one
occurence.

Here is what I did that doesn't work:
Create a defined name, Range1, of the cells above.
Run this formula:
IF(COUNTIF(Range1,1)0,1,0)

The COUNTIF will not work with this kind of defined name where the
cells are scattered and not in a column or row.

Can someone help?

Thanks,

Neal


--
nealgseattle
------------------------------------------------------------------------
nealgseattle's Profile: http://www.excelforum.com/member.php...o&userid=35049
View this thread: http://www.excelforum.com/showthread...hreadid=547933

Is there a way to find a value in a set of cells?
 

Hi!

Try something like this:

SUMPRODUCT(COUNTIF(INDIRECT({"A2","A15","B2","B22" }),1))

Include ALL your cell references inside the Indirect array.

Then wrap that inside an IF:

=IF(SUMPRODUCT(COUNTIF(INDIRECT({"A2","A15","B2"," B22"}),1))0,Do_This,Do_That)

Biff

"nealgseattle"
wrote in message
news:nealgseattle.28sf42_1149273002.8964@excelforu m-nospam.com...

I am trying to find at least one occurence of a specific value in a set
of cells that are going to be scattered in a spreadsheet. Here is a
pseudocode example what I am trying to do:

If any value in
{a2, a15, b2, b22, c5, c7, c9, d45, d55, e1, e17}
= 1
Then perform this action
Else perform this action

The value could appear more than once, and I am testing for only one
occurence.

Here is what I did that doesn't work:
Create a defined name, Range1, of the cells above.
Run this formula:
IF(COUNTIF(Range1,1)0,1,0)

The COUNTIF will not work with this kind of defined name where the
cells are scattered and not in a column or row.

Can someone help?

Thanks,

Neal


--
nealgseattle
------------------------------------------------------------------------
nealgseattle's Profile:
http://www.excelforum.com/member.php...o&userid=35049
View this thread: http://www.excelforum.com/showthread...hreadid=547933

Is there a way to find a value in a set of cells?
 

This looks exactly what I need. Thanks for you help!

Neal


--
nealgseattle
------------------------------------------------------------------------
nealgseattle's Profile: http://www.excelforum.com/member.php...o&userid=35049
View this thread: http://www.excelforum.com/showthread...hreadid=547933

Is there a way to find a value in a set of cells?
 

You're welcome!

Biff

"nealgseattle"
wrote in message
news:nealgseattle.28skwc_1149280503.0707@excelforu m-nospam.com...

This looks exactly what I need. Thanks for you help!

Neal


--
nealgseattle
------------------------------------------------------------------------
nealgseattle's Profile:
http://www.excelforum.com/member.php...o&userid=35049
View this thread: http://www.excelforum.com/showthread...hreadid=547933