James3xt0n
New Member
- Joined
- Jul 5, 2019
- Messages
- 2
I've vigorously searched online and can't find anything to help with my formula dilemma.
I need a formula to search a column/range for any cells (1 or more) with [value], to return (a single selected cell) as true/[value].
I want the formula to ask the question: does any of the cells (1 or more) within the selected range contain this [value]? If it does then return as TRUE or [value].
my intention is to use conditional formatting, using the formula, to create a notification alert/cell on a master sheet to notify which sheet needs attending to.
FOR EXAMPLE:
SHEET1!A1 is selected for formula input
SHEET2!K:K is the selected range
"TRUE" is the value to be searched for
"ALERT" is the value to return as if true.
If any cell (1 or more cells) within the K column on SHEET2 contains "TRUE", then A1 on SHEET1 will return as "ALERT"
SHEET3!E3:F17 is the selected range
"195" is the value to be searched for
"TRUE" is the value to return as if true
If any cell (1 or more cells) within the range E3:F17 on SHEET3 contains "195", then A2 on SHEET1 will return as "TRUE"
The problem I've found on forms and tutorials is that, although multiple cells can be selected it only returns true or false for each cell and have struggled to find a function to ask excel; does any of the cells meet this criteria?
I need a formula to search a column/range for any cells (1 or more) with [value], to return (a single selected cell) as true/[value].
I want the formula to ask the question: does any of the cells (1 or more) within the selected range contain this [value]? If it does then return as TRUE or [value].
my intention is to use conditional formatting, using the formula, to create a notification alert/cell on a master sheet to notify which sheet needs attending to.
FOR EXAMPLE:
SHEET1!A1 is selected for formula input
SHEET2!K:K is the selected range
"TRUE" is the value to be searched for
"ALERT" is the value to return as if true.
If any cell (1 or more cells) within the K column on SHEET2 contains "TRUE", then A1 on SHEET1 will return as "ALERT"
OR
SHEET1!A2 is selected for formula inputSHEET3!E3:F17 is the selected range
"195" is the value to be searched for
"TRUE" is the value to return as if true
If any cell (1 or more cells) within the range E3:F17 on SHEET3 contains "195", then A2 on SHEET1 will return as "TRUE"
The problem I've found on forms and tutorials is that, although multiple cells can be selected it only returns true or false for each cell and have struggled to find a function to ask excel; does any of the cells meet this criteria?
