Conditional Format or Conditional Formula?

[OneTimeUser]

Hi Team,

I need to set up a cell to automatically return a date (held in another cell) based on the text that is entered in another (third) cell.
So for example if C1 text equals "YES", then I need cell B1 to return the date held in cell A1. If C1 is anything else than "YES" then I need the cell to return nothing / stay blank.

The formula I found worked for this was =IF($AC2="YES",$I2," ") in my sheet, however users of the sheet will type directly into the 'B1' cell in this example, which overwrites the formula and stuffs everything up - so I think I need to do this as a conditional formula so it sits in the background for the cell? but i'm not sure if I can program a conditional formula to return a date result from a third cell?

Sorry if this is a bit unrefined, I only dabble in excel land!

 

[Toadstool]

Hi OneTimeUser,

You need to be very careful. You may be a OneTimeUser but Excel can be addictive after just one use.

Conditional Format won't let you change contents of a cell, only some of its formatting. You probably want to lock the cells with formulae and unlock only those the users are allowed to change. Here, let me show you.

Select any cells in your sheet, right-click and Format Cells. Pick the right hand tab "Protection" and you'll see by default all cells are Locked, but not Hidden.

The Locked doesn't apply until you Protect the sheet but first we'd better remove that lock from the cells where the user can enter data, and in this example I'll just let then change column AC for the YES or No.
Select all cells they can change, right-click, Format Cells and on the Protection tab uncheck the Locked and click OK.

Now right-click the sheet name at the bottom of the screen and select Protect Sheet. It will ask for a password and let you change what the user is allowed to do. After you've entered the password it will ask you to retype it to verify. Once you OK that the sheet is locked.

Now the user can still type into those column AC cells but if they try and overtype you formula in column B they'll be prevented from doing so.

Hope this helps.