How do I make a particular column required in Excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smistretta - 24 Apr 2006 17:45 GMT

Our NPO is creating a reimbursement chart in Excel. We're trying to make it
so that if employees do not fill in one particular column entitled
"Projects," they will get an error or will be unable to have a final sum
filled in.

Is there any way I can make this particular column required so that if it is
not filled in, the chart will not complete properly?

Thank you in advance for any advice or help--I'm a bit of a novice to this!

Reply to this Message

Sandy Mann - 24 Apr 2006 19:43 GMT

smistretta,

You don't really give enough information for, (me to give), a real answer
but if the "Projects" column is column G then:

=IF(AND(COUNTA(A5:F5)=6,ISNUMBER(G5)),SUM(F5:G5),IF(COUNTA(A5:F5)=6,"Fill in
'Projects' Column",""))

Will remain with no display until columns A:F have (any) data in them then
it will display "Fill in 'Projetcs' Column"

When the Projects column is filled in as well,(but not if A:F are not also
filled in), then the SUM will be returned.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Our NPO is creating a reimbursement chart in Excel. We're trying to make
> it
[quoted text clipped - 8 lines]
> Thank you in advance for any advice or help--I'm a bit of a novice to
> this!

Reply to this Message

smistretta - 24 Apr 2006 20:04 GMT

In response to Sandy's post:

Thank you for your help. I'm not sure what information you need, so I'll
include what I can:

Projects Column in column J
"Totals" are in Row 22 and the Grand Total cell is L22
First row of data entry strings from B8 to I8
Data rows run from row 8 to row 21

I tried to use your suggestion in my Excel table but it keeps telling me it
is a circular formula. Any thoughts or ideas?

Thank you again!

> smistretta,
>
[quoted text clipped - 22 lines]
> > Thank you in advance for any advice or help--I'm a bit of a novice to
> > this!

Reply to this Message

Sandy Mann - 24 Apr 2006 20:41 GMT

Perhaps its just me but assuming that:

> First row of data entry strings from B8 to I8
> Data rows run from row 8 to row 21

means numbers and does not actually mean strings, then try in B22

=IF(COUNT(B8:B21)=14,SUM(B8:B21),"")

and copy across to I22. In L22 enter:

If it may be that not all cells in B8:B21 will be filled in then use:

=IF(COUNT(B8:B21),SUM(B8:B21),"")

If you really do mean that B8:I21 will have strings then in B22 use:

=IF(COUNTA(B8:B21),COUNTA(B8:B21),"")

In either case in L22 enter:

=IF(AND(COUNT(B22:I22)=8,COUNTA(J8:J21)<>14),"Fill in 'Projects'
Column",IF(AND(COUNT(B22:I22)=8,COUNTA(J8:J21)=14),SUM(B22:I22),""))

If I have not got it right then please do post back correcting my wrong
assumptions.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> In response to Sandy's post:
>
[quoted text clipped - 44 lines]
>> > Thank you in advance for any advice or help--I'm a bit of a novice to
>> > this!

Reply to this Message

smistretta - 26 Apr 2006 19:03 GMT

Sandy-

Thank you so much for your help. I inserted the formula in all of the areas
that you said, but now I'm presented with a new problem: Instead of a number
in the Grand Total cell (L22), it says either 'true' or 'false.'

Is this a problem of using the logic formula? Any new suggestions?

Thank you again!!

> Perhaps its just me but assuming that:
>
[quoted text clipped - 71 lines]
> >> > Thank you in advance for any advice or help--I'm a bit of a novice to
> >> > this!

Reply to this Message