| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF #N/A ( in a range), "ALERT","OK"
I have a rang of cells with formulas in them as below:
=VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF #N/A ( in a range), "ALERT","OK"
Try this:
=IF(COUNTIF(E4:E631,"#N/A"),"ALERT","OK") -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have a rang of cells with formulas in them as below: =VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF #N/A ( in a range), "ALERT","OK"
Or try,
=IF(ISERROR(VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FA LSE)),"Alert","OK") Bam. "Steve" wrote: I have a rang of cells with formulas in them as below: =VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF #N/A ( in a range), "ALERT","OK"
Thank you,
Works great. And so simple too. Thanks again, Steve "T. Valko" wrote: Try this: =IF(COUNTIF(E4:E631,"#N/A"),"ALERT","OK") -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have a rang of cells with formulas in them as below: =VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF #N/A ( in a range), "ALERT","OK"
Can't quite get it to work yet, probably because of the ranges, which I'll
check out. I'm sure it'll work when I play around with it. But the other suggestion is working, and that's one of the great things about Excel, more than one way to achieve what you're looking for. Thanks again, Steve "Bam" wrote: Or try, =IF(ISERROR(VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FA LSE)),"Alert","OK") Bam. "Steve" wrote: I have a rang of cells with formulas in them as below: =VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF #N/A ( in a range), "ALERT","OK"
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... Thank you, Works great. And so simple too. Thanks again, Steve "T. Valko" wrote: Try this: =IF(COUNTIF(E4:E631,"#N/A"),"ALERT","OK") -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have a rang of cells with formulas in them as below: =VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF #N/A ( in a range), "ALERT","OK"
Steve,
Sorry, I mis-read the question a bit. =IF(ISERROR(E4:E631),"Alert","OK") Put this in G2. Cheers, Bam. "Steve" wrote: Can't quite get it to work yet, probably because of the ranges, which I'll check out. I'm sure it'll work when I play around with it. But the other suggestion is working, and that's one of the great things about Excel, more than one way to achieve what you're looking for. Thanks again, Steve "Bam" wrote: Or try, =IF(ISERROR(VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FA LSE)),"Alert","OK") Bam. "Steve" wrote: I have a rang of cells with formulas in them as below: =VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF #N/A ( in a range), "ALERT","OK"
That should do it.
Thanks, "Bam" wrote: Steve, Sorry, I mis-read the question a bit. =IF(ISERROR(E4:E631),"Alert","OK") Put this in G2. Cheers, Bam. "Steve" wrote: Can't quite get it to work yet, probably because of the ranges, which I'll check out. I'm sure it'll work when I play around with it. But the other suggestion is working, and that's one of the great things about Excel, more than one way to achieve what you're looking for. Thanks again, Steve "Bam" wrote: Or try, =IF(ISERROR(VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FA LSE)),"Alert","OK") Bam. "Steve" wrote: I have a rang of cells with formulas in them as below: =VLOOKUP(Data!A4,Table!$A$1:$A$101,1,FALSE). This formula produces either numbers or #N/As in the below range: The range is from E4 through E631. If any of the cells ( E4:E631) have #N/A in them, I would like "ALERT" to show Cell G2, if no #N/A, then "OK" in cell G2. Something like If (E4:E62 contains #N/A, "Alert", "OK") So I don't have to scroll down looking for #N/A 's that may or may not be there. Thanks, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
