I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x" in
column D after all items are received. Any advice is appreciated. Thank you!

Hi,

How are you? In cell D2, enter the following formula

=if(countblank(E2:I2)=0,"x","Information awaited")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need
a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x"
in
column D after all items are received. Any advice is appreciated. Thank
you!

Try this:

=IF(COUNTA(E2:I2),"","X")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need
a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x"
in
column D after all items are received. Any advice is appreciated. Thank
you!

Hi,

Try something like

=IF(COUNTA(E2:I2)<5,"Missing Info","X")

You can replace Missing Info with "" which will show blank until all the
data is recieved.

If this helps, please click the Yes button,

cheers,
Shane Devenshire

"Denise" wrote:

I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x" in
column D after all items are received. Any advice is appreciated. Thank you!

Hi,

Not quite sure if you want the x to appear if the other columns are all full
or all empty.

Try

=IF(COUNTA(E2:I2)=5,"x","")

or

=IF(COUNTA(E2:I2)=5,"","x")

Dave

url:http://www.ureader.com/msg/104241977.aspx

Thank You! It works Perfectly!!

"T. Valko" wrote:

Try this:

=IF(COUNTA(E2:I2),"","X")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need
a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x"
in
column D after all items are received. Any advice is appreciated. Thank
you!

We have a lot of columns that we put notes in because more than one person
uses that spreadsheet. Once an item is received we delete the comments in
that cell. To save the time that it takes to scroll to the right (for 1000's
of employees) and check for missing credentials I thought it would be a cince
if I coded one of the first cells that appears when I open the sheet to let
me know that this file is complete. So to answer your question: I need to
know when all of my flagged cells for that employee have been cleared.

I was able to accomplish that with Biff's code. Yours looks very similar to
his but I have not tried it yet. Thanks for replying all the same!

"Dave Curtis" wrote:

Hi,

Not quite sure if you want the x to appear if the other columns are all full
or all empty.

Try

=IF(COUNTA(E2:I2)=5,"x","")

or

=IF(COUNTA(E2:I2)=5,"","x")

Dave

url:http://www.ureader.com/msg/104241977.aspx

Should I post this as a new question even though it concerns the same project?

Is there any way to use the Date variable to change the color of an entire
row if 30-days have past and there isn't an "x" in my control column?
Basically if all of their items have not been turned in before a certain
time...we will find a more obvious flag like a gray background for that
employee's row?



"T. Valko" wrote:

Try this:

=IF(COUNTA(E2:I2),"","X")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need
a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x"
in
column D after all items are received. Any advice is appreciated. Thank
you!

You can do this using conditional formatting.

Let's assume A1:A10 = dates, B1:B10 = X or empty

Select the range of cells A1:B10
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(TODAY()-30$A1,$B1<"X")
Click the Format button
Select the Patterns tab and pick the fill color of your choice
OK out

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
Should I post this as a new question even though it concerns the same
project?

Is there any way to use the Date variable to change the color of an entire
row if 30-days have past and there isn't an "x" in my control column?
Basically if all of their items have not been turned in before a certain
time...we will find a more obvious flag like a gray background for that
employee's row?



"T. Valko" wrote:

Try this:

=IF(COUNTA(E2:I2),"","X")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I
need
a
visual flag. Usually the items trickle in and I delete my comments in
the
column one-by-one. So I am trying to create a formula that places an
"x"
in
column D after all items are received. Any advice is appreciated.
Thank
you!

Thank you very much -It worked like magic! One more question: How could I add
one more condition to the first code ...=IF(COUNTA(E2:I2),"","X") to allow
the cells with "n/a" to be treated as if they were empty? I can't delete the
"n/a" or it might confuse someone else looking in the spreadsheet.


Valko" wrote:

You can do this using conditional formatting.

Let's assume A1:A10 = dates, B1:B10 = X or empty

Select the range of cells A1:B10
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(TODAY()-30$A1,$B1<"X")
Click the Format button
Select the Patterns tab and pick the fill color of your choice
OK out

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
Should I post this as a new question even though it concerns the same
project?

Is there any way to use the Date variable to change the color of an entire
row if 30-days have past and there isn't an "x" in my control column?
Basically if all of their items have not been turned in before a certain
time...we will find a more obvious flag like a gray background for that
employee's row?



"T. Valko" wrote:

Try this:

=IF(COUNTA(E2:I2),"","X")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I
need
a
visual flag. Usually the items trickle in and I delete my comments in
the
column one-by-one. So I am trying to create a formula that places an
"x"
in
column D after all items are received. Any advice is appreciated.
Thank
you!

I assume N/A is a text entry and not the error value #N/A.

Try this:

=IF(COUNTA(E2:I2)-COUNTIF(E2:I2,"n/a")=0,"X","")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need
a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x"
in
column D after all items are received. Any advice is appreciated. Thank
you!

I assume N/A is a text entry and not the error value #N/A.

Try this:

=IF(COUNTA(E2:I2)-COUNTIF(E2:I2,"n/a")=0,"X","")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
Thank you very much -It worked like magic! One more question: How could I
add
one more condition to the first code ...=IF(COUNTA(E2:I2),"","X") to allow
the cells with "n/a" to be treated as if they were empty? I can't delete
the
"n/a" or it might confuse someone else looking in the spreadsheet.


Valko" wrote:

You can do this using conditional formatting.

Let's assume A1:A10 = dates, B1:B10 = X or empty

Select the range of cells A1:B10
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(TODAY()-30$A1,$B1<"X")
Click the Format button
Select the Patterns tab and pick the fill color of your choice
OK out

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
Should I post this as a new question even though it concerns the same
project?

Is there any way to use the Date variable to change the color of an
entire
row if 30-days have past and there isn't an "x" in my control column?
Basically if all of their items have not been turned in before a
certain
time...we will find a more obvious flag like a gray background for that
employee's row?



"T. Valko" wrote:

Try this:

=IF(COUNTA(E2:I2),"","X")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files.
If
their file is missing something (physical, background check, etc..)
I
need
a
visual flag. Usually the items trickle in and I delete my comments
in
the
column one-by-one. So I am trying to create a formula that places an
"x"
in
column D after all items are received. Any advice is appreciated.
Thank
you!

Many thanks!!! It worked!

"T. Valko" wrote:

I assume N/A is a text entry and not the error value #N/A.

Try this:

=IF(COUNTA(E2:I2)-COUNTIF(E2:I2,"n/a")=0,"X","")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I need
a
visual flag. Usually the items trickle in and I delete my comments in the
column one-by-one. So I am trying to create a formula that places an "x"
in
column D after all items are received. Any advice is appreciated. Thank
you!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
Many thanks!!! It worked!

"T. Valko" wrote:

I assume N/A is a text entry and not the error value #N/A.

Try this:

=IF(COUNTA(E2:I2)-COUNTIF(E2:I2,"n/a")=0,"X","")

--
Biff
Microsoft Excel MVP


"Denise" wrote in message
...
I am working on a spreadsheet that I created to audit employee files. If
their file is missing something (physical, background check, etc..) I
need
a
visual flag. Usually the items trickle in and I delete my comments in
the
column one-by-one. So I am trying to create a formula that places an
"x"
in
column D after all items are received. Any advice is appreciated.
Thank
you!