Making a particular cell or column required

[smistretta]

I posted this a few days ago and got some good responses, but nothing that's
actually accomplishing what I had hoped (maybe it really just can't be done)

The specifics are:
Creating an NPO reimbursement form where, in order for the form to be valid,
the employee needs to enter a project code into a column entitled '"Project"

The goal is to make it so that if this column is not filled in, an error
message will show up in the Grand Total or the person will be unable to
complete any more cells--sort of an enforced entry

Excel details:
Project column is column J
Grand Total cell is L22
Columns where expenses are entered are from B to I (Project is last column)
Rows where expenses are entered are from 8 to 21, with 22 being each
column's total
Not all rows/cells have to be filled in for the form to be valid but if a
row is used, it must (ideally) have a required Project code

I have tried:
1. An IF statement in the Grand Total cell (L22) but this leaves me with a
'true' or 'false' entry, not a number as it needs to be
2. A Validation where the employee has to select from a list of project
codes but an erro will only show up if they type an incorrect code, not if
they leave it blank

I'm new to this, so any other thoughts, ideas or help at all will b greatly
appreciated. Thank you in advance!

[Dave O]

You might revise the IF formula in the Grand Total cell to say
IF(Project cell = "","Please enter a valid project number,sum(range of
numbers)).

[Miguel Zapico]

You may use an array formula for the sums. For example, for column B, you
can enter in cell B22 the following:
=SUM(B8:B21*(J8:J21<>""))
Hitting Crtl+Shift+Enter to tell Excel that it is an array formula (you will
see curly brackets around the formula in the bar). This will add only the
rows that have a value on the column J.

Hope this helps,
Miguel.

"smistretta" wrote:

> I posted this a few days ago and got some good responses, but nothing that's
> actually accomplishing what I had hoped (maybe it really just can't be done)
>
> The specifics are:
> Creating an NPO reimbursement form where, in order for the form to be valid,
> the employee needs to enter a project code into a column entitled '"Project"
>
> The goal is to make it so that if this column is not filled in, an error
> message will show up in the Grand Total or the person will be unable to
> complete any more cells--sort of an enforced entry
>
> Excel details:
> Project column is column J
> Grand Total cell is L22
> Columns where expenses are entered are from B to I (Project is last column)
> Rows where expenses are entered are from 8 to 21, with 22 being each
> column's total
> Not all rows/cells have to be filled in for the form to be valid but if a
> row is used, it must (ideally) have a required Project code
>
> I have tried:
> 1. An IF statement in the Grand Total cell (L22) but this leaves me with a
> 'true' or 'false' entry, not a number as it needs to be
> 2. A Validation where the employee has to select from a list of project
> codes but an erro will only show up if they type an incorrect code, not if
> they leave it blank
>
> I'm new to this, so any other thoughts, ideas or help at all will b greatly
> appreciated. Thank you in advance!

[Ronco]

Not sure if you've tried this with data validation, but, clear the Ignore
blank check box in the validation screen. I copied this from Help in Excel:
4. Specify whether the cell can be left blank:
If you want to allow blank (null) values, select the Ignore blank check box.
If you want to prevent entry of blank (null) values, clear the Ignore blank
check box.
Note If your allowed values are based on a cell range with a defined name,
and there is a blank cell anywhere in the range, setting the Ignore blank
check box allows any values to be entered in the validated cell. This is also
true for any cells referenced by validation formulas: if any referenced cell
is blank, setting the Ignore blank check box allows any values to be entered
in the validated cell.



"smistretta" wrote:

> I posted this a few days ago and got some good responses, but nothing that's
> actually accomplishing what I had hoped (maybe it really just can't be done)
>
> The specifics are:
> Creating an NPO reimbursement form where, in order for the form to be valid,
> the employee needs to enter a project code into a column entitled '"Project"
>
> The goal is to make it so that if this column is not filled in, an error
> message will show up in the Grand Total or the person will be unable to
> complete any more cells--sort of an enforced entry
>
> Excel details:
> Project column is column J
> Grand Total cell is L22
> Columns where expenses are entered are from B to I (Project is last column)
> Rows where expenses are entered are from 8 to 21, with 22 being each
> column's total
> Not all rows/cells have to be filled in for the form to be valid but if a
> row is used, it must (ideally) have a required Project code
>
> I have tried:
> 1. An IF statement in the Grand Total cell (L22) but this leaves me with a
> 'true' or 'false' entry, not a number as it needs to be
> 2. A Validation where the employee has to select from a list of project
> codes but an erro will only show up if they type an incorrect code, not if
> they leave it blank
>
> I'm new to this, so any other thoughts, ideas or help at all will b greatly
> appreciated. Thank you in advance!