Ok, I'm not sure what all your refeneces are to in this formula:

=IF((AND(($EV8=1),(OR(('TRAINING STATUS REPORT'!Z8=1),('TRAINING STATUS
REPORT'!AE8=1))))),1,0)

But, it sounds like you need AND rather than OR:

=IF(AND($EV8=1,TSR!Z8=1,TSR!AE8=1),1,0)

Or:

=--AND($EV8=1,TSR!Z8=1,TSR!AE8=1)



--
Biff
Microsoft Excel MVP


"Bernie R." <Bernie wrote in message
...
Great! That helps, but my real problem is with the second formula...$EV8
would be the value from the formula you shortened below. example: a
soldier
has an M4 as a primary weapon and completed day, nbc, but not night; he
has a
M9 as a secondary weapon and completed day, nbc, and night. The way the
second formula is written the M4 stats show day, nbc, and night completed
because it's seeing the night date in the secondary weapon as completed.

Thanks for your assistance.

"T. Valko" wrote:

You can reduce this:

=IF(OR(('TRAINING STATUS REPORT'!Y8="M4"),('TRAINING STATUS
REPORT'!Y8="M16A1"), ('TRAINING STATUS
REPORT'!Y8="M16A2-A4"),('TRAINING
STATUS REPORT'!Y8="M203"), ('TRAINING STATUS
REPORT'!AD8="M4"),('TRAINING
STATUS REPORT'!AD8="M16A1"), ('TRAINING STATUS REPORT'!AD8="M16A2-A4"),
('TRAINING STATUS REPORT'!AD8="M203")),1,0)

To:

TRAINING STATUS REPORT = TSR

=IF(OR(TSR!Y8={"M4","M16A1","M16A2-A4","M203"},TSR!AD8={"M4","M16A1","M16A2-A4","M203"}),1,0)


--
Biff
Microsoft Excel MVP


"Bernie R." <Bernie wrote in message
...
I have a spreadsheet that tracks weapon type and their qualification
steps
in
two categories (primary and secondary weapon). There are 4 columns in
each
category: weapon type, Day, NBC, Night.

I had this formula in the weapon type column:
=IF(OR(('TRAINING STATUS REPORT'!Y8="M4"),('TRAINING STATUS
REPORT'!Y8="M16A1"), ('TRAINING STATUS
REPORT'!Y8="M16A2-A4"),('TRAINING
STATUS REPORT'!Y8="M203"), ('TRAINING STATUS
REPORT'!AD8="M4"),('TRAINING
STATUS REPORT'!AD8="M16A1"), ('TRAINING STATUS REPORT'!AD8="M16A2-A4"),
('TRAINING STATUS REPORT'!AD8="M203")),1,0)

I have this formula in the Day, NBC and Night pointing to the
respective
data:
=IF((AND(($EV8=1),(OR(('TRAINING STATUS REPORT'!Z8=1),('TRAINING
STATUS
REPORT'!AE8=1))))),1,0)

The problem I'm having is that if I have a M4 weapon in the primary
category
and there's a date in the Day and NBC columns, leaving Night
blank...and
the
person qualified on a secondary weapon completely. I'm not showing the
blank
in the Night column for the M4 because the secondary weapons data is
showing
a date in all three of its columns.

I am using a "formula" worksheet to track the weapons qual data
worksheet,
and the "formula" worksheet feeds the summary quad worksheet.