Multiple 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.

Multiple cell reference IF function
 

One way ..

Try in B34:
=IF(SUM(COUNTBLANK(O12:O13),COUNTBLANK(O23:O24),CO UNTBLANK(O36:O37),COUNTBLANK(O45:O46),COUNTBLANK(O 56:O57),COUNTBLANK(O65:O66))0,"Incomplete
data entry",SUM(O12:O13,O23:O24,O36:O37,O45:O46,O56:O57 ,O65:O66))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Colinhp" wrote:
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.

Multiple cell reference IF function
 

Hi Colin

One way would be to create a named range called Mydata using
InsertNameDefine and including your range of 12 cells as the Refers to
item.
Then
=IF(COUNTA(Mydata)<12,"You need more data",your_formula)

--
Regards

Roger Govier


"Colinhp" wrote in message
...
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.

Multiple cell reference IF function
 

Hi Roger,

Thank you for your help.

"Roger Govier" wrote:

Hi Colin

One way would be to create a named range called Mydata using
InsertNameDefine and including your range of 12 cells as the Refers to
item.
Then
=IF(COUNTA(Mydata)<12,"You need more data",your_formula)

--
Regards

Roger Govier


"Colinhp" wrote in message
...
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.

Multiple cell reference IF function
 

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))


--
Regards

Roger Govier


"Colinhp" wrote in message
...
Hi Roger,

Thank you for your help.

"Roger Govier" wrote:

Hi Colin

One way would be to create a named range called Mydata using
InsertNameDefine and including your range of 12 cells as the Refers
to
item.
Then
=IF(COUNTA(Mydata)<12,"You need more data",your_formula)

--
Regards

Roger Govier


"Colinhp" wrote in message
...
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.

Multiple cell reference IF function
 

"Roger Govier" wrote:
.. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))

Nice, neat solution, Roger !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Multiple cell reference IF function
 

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.

"Max" wrote:

"Roger Govier" wrote:
.. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))

Nice, neat solution, Roger !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Multiple cell reference IF function
 

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

--
Regards

Roger Govier


"Colinhp" wrote in message
...
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.

"Max" wrote:

"Roger Govier" wrote:
.. =IF(COUNTA(Mydata)<12,"You need more data",SUM(Mydata))

Nice, neat solution, Roger !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---