Formula or VBA code to revert back to a formula if manually entered data is deleted

[RakaRatcliffe]

Hi all,

So basically I have created a spreadsheet to use as a rota calendar for work. I have used / changed a formula I found on another spreadsheet to automatically populate my calendar with the rota for the year, based on which rota is selected and the start date for that rota and the year.

The formula populates the cells with either "D", "N" or "R" (to symbolise Days, Nights & Rota Days respectively)

I want users to be able to then manually override the rota and input other data, for example "AL" for an annual leave day, "SK" for sickness, etc.

My problem is that if the user deletes the manually inputted data (i.e. deletes "AL" because someone cancels their leave) that the cell has now lost it's formula to put the rota back in.

I would like to avoid using VBA if I can.

I am able to have a 2nd row for each person, which I can hide and lock when the spreadsheet is made, and this row could contain the background formula.

So, A1 is the cell I want the user to be able to change and A2 will be the cell in the hidden row underneath that contains the background formula.

Thanks in advance.

 

[cooper645]

Could you not use data validation?

I have tested it works with the following

Data validation > List > Source
AL,SK,=SUM($A$3:$A$4)

You should be able to replace my simple sum formula with your formula that populates D,N & R.
If you set this up in the first cell and select the formula from the drop down, then copy it across the rest of the cells that require it, they should be correctly populated as D,N,R.
Then the user simply selects the day they are taking Leave and selects AL from the dropdown. If it later changes then simply reselect the formula from the drop down.

Note: the formula must not be the first value in the drop down list, and must not refer to non absolute references.


If you could supply the formula you use for the D,N,R then this could possibly be worked further


HTH

Coops

 

[RakaRatcliffe]

Hey Coops, sorry for the delayed reply. Thanks for your help, but I have found some VBA code to do the trick for me. Cheers