place an "x" in D2 if E2-I2 are empty of text??

[Denise]

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!

 

[Ashish Mathur]

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

 

[T. Valko]

Try this:

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

 

[Shane Devenshire]

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]

Thank You! It works Perfectly!!

Try this:

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

 

[Denise]

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!

 

[Denise]

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]

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 Format>Conditional 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

 

[Denise]

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.

 

[T. Valko]

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","")

 

[T. Valko]

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","")

 

[Denise]

Many thanks!!! It worked!

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","")

 

[T. Valko]

You're welcome. Thanks for the feedback!