 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2006Multiple cell reference IF function
I want a SUM formula to occur in cell B34 that includes the following cells, providing they are not blank. O12,O13,O23,O24,O36,O37,O45,O46,O56,O57,O65 and O66. These cells have a drop-down selection of numbers from 1-5, and have data validation and conditional formatting set to red prior to them choosing from the drop-down list. I can nest ISBLANKO12:O13 in an IF function and if False have the SUM formula occur, but how do I also include these cells (O12,O13,O23,O24,O36,O37,O45,O46,O56,O57,O65 and O66) in the IF function too? If any of these cells are left blank by the user I want a message to appear informing them that they must enter data in all of the cells before they find a result shown in B34. This message is not to be shown before the user starts entering data in the cells. One way .. Try in B34: =IF(SUM(COUNTBLANK(O12:O13),COUNTBLANK(O23:O24),COUNTBLANK(O36:O37),COUNTBLANK(O45:O46),COUNTBLANK(O56:O57),COUNTBLANK(O65:O66))>0,"Incomplete data entry",SUM(O12:O13,O23:O24,O36:O37,O45:O46,O56:O57,O65:O66))  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > I want a SUM formula to occur in cell B34 that includes the following cells, > providing they are not blank. O12,O13,O23,O24,O36,O37,O45,O46,O56,O57,O65 [quoted text clipped - 9 lines] > This message is not to be shown before the user starts entering data in the > cells. Hi Colin One way would be to create a named range called Mydata using Insert>Name>Define and including your range of 12 cells as the Refers to item. Then =IF(COUNTA(Mydata)<12,"You need more data",your_formula) SignatureRegards Roger Govier >I want a SUM formula to occur in cell B34 that includes the following >cells, [quoted text clipped - 17 lines] > in the > cells. Hi Roger, Thank you for your help. > Hi Colin > [quoted text clipped - 25 lines] > > in the > > cells. Hi Colin Thanks for the feedback. If I had read your question thoroughly I would have realised what the formula was that you wanted and my response should have been =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata)) SignatureRegards Roger Govier > Hi Roger, > [quoted text clipped - 35 lines] >> > in the >> > cells. > .. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata)) Nice, neat solution, Roger ! SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Hi, The nominated cell for the error data is too small for the user to see. Is there a simple solution to having a message box appear when the If function returns the message? I have posted this question in the Excel programming section, prior to me reading your second response. Data Validation appears not to work as the cell is not selected by the user. > > .. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata)) > > Nice, neat solution, Roger ! Hi Colin Try changing to =IF(COUNTA(Mydata)<12,"DATA!!!",SUM(Mydata)) Then apply conditional formatting to cell Formula Is =NOT(ISNUMBER(cellref)) where cellref is the cell where your formula is entered Format Font RED, Bold SignatureRegards Roger Govier > Hi, > [quoted text clipped - 11 lines] >> >> Nice, neat solution, Roger ! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.