Cells Defaulting to Original Formula if Blank

Hello and thank you for taking the time to read (and answer) my question.

I'm trying to create a data spreadsheet where one of the functions is to populate a series of columns with dates (added days, ie. 15 days, 7 days, etc.) based on the originally entered date (let's call that Date A, with the series of dates as Date B, Date C, etc.). Each following date down the series is added from its predecessor, so Date B is 14 days from Date A, Date C is 7 days from Date B, and so forth. Pretty easy stuff by simply using =A1+XX where XX is the number of days, etc.

The dilemma:

  • Each of the dates must have the capability to be manually overridden, with the dates in the series to reflect that change. In other words, if there's a 15 day count between Date B and C, and Date B was manually overridden from 1/1/2014 in and changed to 1/15/2014, then Date C should also change from 1/15/2014 to 1/30/2014
  • If a user was to change his mind and delete the manually entered date, it should revert back to using the original formula to acquire a date. Another words, if the user was to simply delete the data from the cell, ie. the cell is blank, the original formula should now be back in place to acquire data.
  • I'd also like to highlight any of the dates generated by a formula to be bold-italicized and a different color as well (ie. blue)

I managed to get the color change and the formulas all layed out, but I'm not sure how I can bring back the formula if manual data is entered and then deleted. I assume an IF ELSE can do this via VBA, but not sure how.

Can anyone help? Thank you in advance!

Answers


One way to do this without using VBA is this.

You can put one cell (I am calling this "manual override cell" or MOC) next to the cell where the formula is placed (formula cell or FC). If the user wants to input, he can input in the MOC. The FC then would contain an isblank function which checks if the MOC contains any value. If there is a value, that value overrides the output of FC. If the MOC is blank, then the formula overrides.

Something like =if(isblank(MOC),(FC-1)+xx,MOC) where (FC -1) is the previous value of FC in the series.

Does this work?


Need Your Help

insert a node through webservice in drupal 7

web-services drupal service drupal-7

i want to insert a node through webservice in drupal 7 and how the service url for insert node looks in drupal 7.

authentication against ADFS, authorization against sql server

c# asp.net authentication authorization adfs

after several days of searching, reading and trial and error i definitely need some help.