Auto Fill "Missing" based on two dates values

**Tejas.T** · 10-29-2012, 02:23 AM

Hi All,

I have two date columns.

My requirement is, in the 2nd column word "Missing" should be filled if difference between [Today] and [Date in 1st column] is more than two days. Also user may enter some date value in 2nd column. In that case "Missing" should not be filled.

I can use 1 or more columns for intermediate calculations.

I have attached a sample file. In that cell "B3" should be auto-filled with "Missing" but not cells "B5"

PLEASE have a look and help me!

**Dave H9** · 10-29-2012, 03:37 AM

Hi Tejas,

The formula would be something like -

Please Login or Register  to view this content.

Please note you cannot have both a formula and a user inputted value in the same cell. If a user manually inputs a date then the formula will be lost.

Hope that helps

Dave H

**Tejas.T** · 10-29-2012, 10:06 AM

Hi Dave H,

Thank you very much. Formula is correct and also it avoided circular reference issue.

I modified it little bit to avoid filling "Missing" if Cell in column A is blank

Please Login or Register  to view this content.

I also know macro. So I think in Cell change event I can check if user has deleted the cell content I can re-fill this formula again in that particular cell.

I can do to that extent. But don't know how to handle if user deletes a Range which contains some cells from column B.

Can you please help in that?

**Dave H9** · 10-29-2012, 10:50 AM

Probably not the most efficient code but try this -

Please Login or Register  to view this content.

Try it out before considering calling it from the worksheet change event.

Hope that helps.

Dave H

**Tejas.T** · 10-31-2012, 02:36 AM

Thank you.

But now I think the better way would be to provide two columns.

Hey do you know how to hide/password protect the formula bar? So that user can not see the formula I have used.

**Dave H9** · 10-31-2012, 04:22 AM

Hi Tejas,

Hope you are sorted on the "Auto Fill" problem. If so please mark this thread as solved and start a new thread for your new question.

Many thanks

Dave H