MS Excel - Keep Formulas in cell 1

[Knicks]

Excel 2010

An overview of what I am trying to do. I have form that users are going to fill out with a bunch of IF formulas. The formulas are dropping off when the IF statement is not satisfied. I want the users to be able to use the spreadsheet multiple times so I don't want to lose the formulas under any condition.

1. User fills out form
2. Clicks button to SAVE, Email a copy, & clears all non column header data except formulas

Column A, B, C, D, ect....

In Column B4 it will say =IF($A4 = "Yes", B3,"")
(repeated below) =IF($A5 = "Yes", B4,"")
(repeated across) =IF($A4 = "Yes", C3,"")
(repeated below) =IF($A5 = "Yes", C4,"")

This will be repeated as concept across and down to column M and through row 999.

The first problem is if $A is "NO" then the True column loses the IF formula and just ends up with the user inputted data. I don't want to lose the formula. And then ultimately I want to CLEAR all the cells besides A2: CJ2 but leave the formulas in tack. I've done a fair amount of VBA programming in Access but Excel not so much. Any help would be appreciated.

Thank you,

 

[Knicks]

Skip,

Are you saying that they can email the work without the need to SAVE so then if it closes without saving everything will be back to the original opening state?

 

[SkipVought]

Why don't you try it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Knicks]

Skip,

Simple & Brilliant. That works. I let Excel get the best of me

 

[SkipVought]

In addition, you can save the workbook as Read Only w/ Read-Write given a Password.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Knicks]

Thanx.

Yes I put the Read Only tag on it to keep from any manual saves. I've tried everything under the sun to get the Excel window to close on completion of code but it stays open. Under further review perhaps thats for the best as they could concievably have their Excel shopping list open or something and then I'd be closing the program down completely. Its not like Access where you would assume Quit is quit. Any reason why the various quits/close doesn't make the program window shut?

 

[SkipVought]

application.quit

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Knicks]

I tried that ~ its the last line of code on my button. Leaves the shell of Excel still open