Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
Hi all, hope someone can help with this - it's driving me crazy.
Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
Assuming all cells will have some value/text in them, you could try:
=IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="", B11="",B13=""),"Please ensure all fields are completed"","")) Hope this helps. -- John C "GillianX" wrote: Hi all, hope someone can help with this - it's driving me crazy. Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
That's the thing, some of the cells might not have text (if the user, say,
overlooks a field) so that's why I need a "please ensure all fields are completed" message so that they do get completed and aren't left blank. Hope you can help. "John C" wrote: Assuming all cells will have some value/text in them, you could try: =IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="", B11="",B13=""),"Please ensure all fields are completed"","")) Hope this helps. -- John C "GillianX" wrote: Hi all, hope someone can help with this - it's driving me crazy. Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
If all the fields are 'supposed' to have text, then my formula will work.
Essentially, once B15 is populated with anything, it will then check to see if any of the other fields are blank. If any of the other fields are blank, it will post the message, if all of the other fields have something in there (be it a number, text, etc), then nothing will display (as everything has been populated). My suggestion, try out the formula I gave you, and let us know if it works. -- John C "GillianX" wrote: That's the thing, some of the cells might not have text (if the user, say, overlooks a field) so that's why I need a "please ensure all fields are completed" message so that they do get completed and aren't left blank. Hope you can help. "John C" wrote: Assuming all cells will have some value/text in them, you could try: =IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="", B11="",B13=""),"Please ensure all fields are completed"","")) Hope this helps. -- John C "GillianX" wrote: Hi all, hope someone can help with this - it's driving me crazy. Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
Thanks John C that works!
"John C" wrote: If all the fields are 'supposed' to have text, then my formula will work. Essentially, once B15 is populated with anything, it will then check to see if any of the other fields are blank. If any of the other fields are blank, it will post the message, if all of the other fields have something in there (be it a number, text, etc), then nothing will display (as everything has been populated). My suggestion, try out the formula I gave you, and let us know if it works. -- John C "GillianX" wrote: That's the thing, some of the cells might not have text (if the user, say, overlooks a field) so that's why I need a "please ensure all fields are completed" message so that they do get completed and aren't left blank. Hope you can help. "John C" wrote: Assuming all cells will have some value/text in them, you could try: =IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="", B11="",B13=""),"Please ensure all fields are completed"","")) Hope this helps. -- John C "GillianX" wrote: Hi all, hope someone can help with this - it's driving me crazy. Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
You're welcome, and thank you for the feedback.
-- John C "GillianX" wrote: Thanks John C that works! "John C" wrote: If all the fields are 'supposed' to have text, then my formula will work. Essentially, once B15 is populated with anything, it will then check to see if any of the other fields are blank. If any of the other fields are blank, it will post the message, if all of the other fields have something in there (be it a number, text, etc), then nothing will display (as everything has been populated). My suggestion, try out the formula I gave you, and let us know if it works. -- John C "GillianX" wrote: That's the thing, some of the cells might not have text (if the user, say, overlooks a field) so that's why I need a "please ensure all fields are completed" message so that they do get completed and aren't left blank. Hope you can help. "John C" wrote: Assuming all cells will have some value/text in them, you could try: =IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="", B11="",B13=""),"Please ensure all fields are completed"","")) Hope this helps. -- John C "GillianX" wrote: Hi all, hope someone can help with this - it's driving me crazy. Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
Just to ask, is it possible to utilise this formula for only one entry e.g.
If cell B19 is left blank, then cell B20 is populate with a "Please select an option" response? "John C" wrote: You're welcome, and thank you for the feedback. -- John C "GillianX" wrote: Thanks John C that works! "John C" wrote: If all the fields are 'supposed' to have text, then my formula will work. Essentially, once B15 is populated with anything, it will then check to see if any of the other fields are blank. If any of the other fields are blank, it will post the message, if all of the other fields have something in there (be it a number, text, etc), then nothing will display (as everything has been populated). My suggestion, try out the formula I gave you, and let us know if it works. -- John C "GillianX" wrote: That's the thing, some of the cells might not have text (if the user, say, overlooks a field) so that's why I need a "please ensure all fields are completed" message so that they do get completed and aren't left blank. Hope you can help. "John C" wrote: Assuming all cells will have some value/text in them, you could try: =IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="", B11="",B13=""),"Please ensure all fields are completed"","")) Hope this helps. -- John C "GillianX" wrote: Hi all, hope someone can help with this - it's driving me crazy. Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF / OR Function
=IF(B19="","Please select...","")
The second "" can be whatever you wish it to be, including a vlookup. -- John C "GillianX" wrote: Just to ask, is it possible to utilise this formula for only one entry e.g. If cell B19 is left blank, then cell B20 is populate with a "Please select an option" response? "John C" wrote: You're welcome, and thank you for the feedback. -- John C "GillianX" wrote: Thanks John C that works! "John C" wrote: If all the fields are 'supposed' to have text, then my formula will work. Essentially, once B15 is populated with anything, it will then check to see if any of the other fields are blank. If any of the other fields are blank, it will post the message, if all of the other fields have something in there (be it a number, text, etc), then nothing will display (as everything has been populated). My suggestion, try out the formula I gave you, and let us know if it works. -- John C "GillianX" wrote: That's the thing, some of the cells might not have text (if the user, say, overlooks a field) so that's why I need a "please ensure all fields are completed" message so that they do get completed and aren't left blank. Hope you can help. "John C" wrote: Assuming all cells will have some value/text in them, you could try: =IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="", B11="",B13=""),"Please ensure all fields are completed"","")) Hope this helps. -- John C "GillianX" wrote: Hi all, hope someone can help with this - it's driving me crazy. Have a straight forward list of cells, name, address, company name etc, which requires the user to type in text or select from a drop down list to complete. Simple enough. I have the following formula which appears in a separate box if any of the required fields are left blank. However, when a user first enters the sheet, this message appears automatically because all the fields are already blank. =IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="" ,B15=""),"Please ensure all fields are completed","") What I would really like to do is have this message appear at the end of non-completion of cell B15 rather than be visible all the time. Hope someone can help. Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |