|
hide "#VALUE!"
My worksheet is linked to a web query and the value of many cells is
"#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
"Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
Sorry, I don't understand your response? Not sure how to find the message?
"Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
Try using conditional formatting with a formula of =ISERROR(A1) and a font
colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
I tried your suggestion, unfortunately did not work.
"Bob Phillips" wrote: Try using conditional formatting with a formula of =ISERROR(A1) and a font colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
Did you select al the cells, change the condition type to Formula Is, and
the formula to the first cell in your selection? -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... I tried your suggestion, unfortunately did not work. "Bob Phillips" wrote: Try using conditional formatting with a formula of =ISERROR(A1) and a font colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
Yes, I believe I have done all of that. Perhaps the problem is that I don't
have a formula in the cells, it is an msn stock quote web query. Maybe the conditional format does not recognize that? Just to confirm, my worksheet has linked values in cells a1:g50. In column D, I would like to hide all "#VALUE!" messages. I have selected all cells in column d and set conditional format to "formula is", =iserror(a1), and font color to white. This is still not working. Thanks again. "Bob Phillips" wrote: Did you select al the cells, change the condition type to Formula Is, and the formula to the first cell in your selection? -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... I tried your suggestion, unfortunately did not work. "Bob Phillips" wrote: Try using conditional formatting with a formula of =ISERROR(A1) and a font colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
No, if it is column D you want to hide, set the formula to =ISERROR(D1),
assuming D1 is the first cell in your selection. The fact that it is formulas doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Yes, I believe I have done all of that. Perhaps the problem is that I don't have a formula in the cells, it is an msn stock quote web query. Maybe the conditional format does not recognize that? Just to confirm, my worksheet has linked values in cells a1:g50. In column D, I would like to hide all "#VALUE!" messages. I have selected all cells in column d and set conditional format to "formula is", =iserror(a1), and font color to white. This is still not working. Thanks again. "Bob Phillips" wrote: Did you select al the cells, change the condition type to Formula Is, and the formula to the first cell in your selection? -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... I tried your suggestion, unfortunately did not work. "Bob Phillips" wrote: Try using conditional formatting with a formula of =ISERROR(A1) and a font colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
Great, sorry for the confusion. Thanks very much for your assistance.
"Bob Phillips" wrote: No, if it is column D you want to hide, set the formula to =ISERROR(D1), assuming D1 is the first cell in your selection. The fact that it is formulas doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Yes, I believe I have done all of that. Perhaps the problem is that I don't have a formula in the cells, it is an msn stock quote web query. Maybe the conditional format does not recognize that? Just to confirm, my worksheet has linked values in cells a1:g50. In column D, I would like to hide all "#VALUE!" messages. I have selected all cells in column d and set conditional format to "formula is", =iserror(a1), and font color to white. This is still not working. Thanks again. "Bob Phillips" wrote: Did you select al the cells, change the condition type to Formula Is, and the formula to the first cell in your selection? -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... I tried your suggestion, unfortunately did not work. "Bob Phillips" wrote: Try using conditional formatting with a formula of =ISERROR(A1) and a font colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
I have tried this and have only one problem - some of the cells in the row do
not return an error. When I applied this conditional formatting to the cells without errors, the text in these cells disappeared as well. How do I get this to applu on a cell by cell basis. Thanks. "Bob Phillips" wrote: No, if it is column D you want to hide, set the formula to =ISERROR(D1), assuming D1 is the first cell in your selection. The fact that it is formulas doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Yes, I believe I have done all of that. Perhaps the problem is that I don't have a formula in the cells, it is an msn stock quote web query. Maybe the conditional format does not recognize that? Just to confirm, my worksheet has linked values in cells a1:g50. In column D, I would like to hide all "#VALUE!" messages. I have selected all cells in column d and set conditional format to "formula is", =iserror(a1), and font color to white. This is still not working. Thanks again. "Bob Phillips" wrote: Did you select al the cells, change the condition type to Formula Is, and the formula to the first cell in your selection? -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... I tried your suggestion, unfortunately did not work. "Bob Phillips" wrote: Try using conditional formatting with a formula of =ISERROR(A1) and a font colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
Disregard - just figured it out. I was dragging ($D$1).
"Forrest" wrote: I have tried this and have only one problem - some of the cells in the row do not return an error. When I applied this conditional formatting to the cells without errors, the text in these cells disappeared as well. How do I get this to applu on a cell by cell basis. Thanks. "Bob Phillips" wrote: No, if it is column D you want to hide, set the formula to =ISERROR(D1), assuming D1 is the first cell in your selection. The fact that it is formulas doesn't matter. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Yes, I believe I have done all of that. Perhaps the problem is that I don't have a formula in the cells, it is an msn stock quote web query. Maybe the conditional format does not recognize that? Just to confirm, my worksheet has linked values in cells a1:g50. In column D, I would like to hide all "#VALUE!" messages. I have selected all cells in column d and set conditional format to "formula is", =iserror(a1), and font color to white. This is still not working. Thanks again. "Bob Phillips" wrote: Did you select al the cells, change the condition type to Formula Is, and the formula to the first cell in your selection? -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... I tried your suggestion, unfortunately did not work. "Bob Phillips" wrote: Try using conditional formatting with a formula of =ISERROR(A1) and a font colour of white. -- HTH RP (remove nothere from the email address if mailing direct) "Brian" wrote in message ... Sorry, I don't understand your response? Not sure how to find the message? "Alan" wrote: "Brian" wrote in message ... My worksheet is linked to a web query and the value of many cells is "#VALUE!". The message is "value used in formula is wrong data type". Due to the nature of the web query, I can live with this without making changes however I would simply like to "hide" or show a blank cell if the cell shows this message. I apprectiate suggestions to do this. Thank you. |
| All times are GMT +1. The time now is 10:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com