Making a column or cell 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!

 

[Pete_UK]

You could apply conditional formatting to the project cells in column J
such that they are given a bright yellow background if empty and other
data is present on that row. In data validation you can choose to
ignore blanks (or not) and set an action if the cell is not valid. You
can change your SUM() statement in L22 to an IF statement - along the
lines of =IF(project codes missing, "Please complete
codes",SUM(range)).

Hope this gives you a few more ideas.

Pete

 

[smistretta]

Thank you for the suggestions.

Any idea how I actually write "Project codes missing"? It seems to want a
value from me there. My current IF statement looks like
=IF(J8:J21)="","Please enter Project Code",SUM(B22:J22))

Thanks!

 

[gls858]

Thank you for the suggestions.

Any idea how I actually write "Project codes missing"? It seems to want a
value from me there. My current IF statement looks like
=IF(J8:J21)="","Please enter Project Code",SUM(B22:J22))

Thanks!

Hard to tell if it there or not but I think you need a space between
the first two double quotes to signify blank

gls858

 

[Pete_UK]

You could try this:

=IF(COUNTA(J8:J21)>0,SUM(B22:J22),"Please enter Project Code")

although this doesn't really check for the existence of the code, just
that something is in the range J8:J21. If those cells are validated, so
that only project codes from your list could be entered, that would
help, but the formula doesn't check that there is a project code for
every line completed. One way that you might achieve this is to use
COUNTA( ) on each of your columns B to I, i.e. =COUNTA(B8:B21) etc -
this could be in a hidden row, say row 50. Then you could amend the
formula above to:

=IF(COUNTA(J8:J21)=MAX(B50:I50),SUM(B22:J22),"Please enter Project
Code")

Again, not foolproof, but an improvement.

Hope this helps.

Pete