Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel find & sort one item in all the cells in a column? | New Users to Excel | |||
How do I find the minimum NONBLANK value in a sequence of cells? | New Users to Excel | |||
How to find linked cells | Excel Discussion (Misc queries) | |||
Find - Entire Cells only | Excel Discussion (Misc queries) | |||
Find cells with ? | Excel Discussion (Misc queries) |