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,

 

[SkipVought]

hi,

form that users are going to fill out with a bunch of IF formulas.

Now THERE'S a disaster waiting to happen!

Are you really wanting users to enter FORMULAS?

Your process is very difficult to understand.

An actual example would be very helpful.

Skip,

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

 

[Knicks]

Measurement Only Audit Date
No 7/25/2013
Yes 7/18/2013
Yes 7/18/2013 <<< IF($A4 = "Yes", B3,"")
Yes 7/18/2013

So Audit Date (B1). The code would start for B3 and so on. The example shows that in B4, IF A4=YES put in results from B3, else it would be blank and type your own result. When A4=NO and the user puts in his own result for B4 then the IF code goes away and just the result shows. You lose the IF code for that cell. If I choose YES in A4, the code in B4 remains. I want to keep the code because ultimately I want to be able to email the workbook and then have it clear the data but keep all the codes. There will be similar type code going down and across. Essentially always tied to the A column "YES" and its corresponding row.

 

[SkipVought]

So exactly what range of cells can the user enter data in?

Do any of these data entry ranges have pre-existant formulas?

Is every other cell not in these ranges, protected from data entry?

Skip,

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

 

[Knicks]

Measurement Audit Date
No 7/25/2013
Yes 7/18/2013
Yes 7/18/2013 <<< IF($A4 = "Yes", B3,"")
Yes 7/18/2013

The Measurement (A) column you can choose YES/NO from drop down. The Audit Date (B) column takes the B cell date 1 cell above when the (A) column = YES. So it auto fills the (B) column for you if (A) is YES. But if (A) is NO then no data is put in the Audit Date (B) column and you can free type. The IF code I have shown is for the Audit Date (B) column.

When NO is chosen for Measurement no data is put in the Audit Date but the IF statement for the (B) cell is then gone. Even if I change my Measurement back to YES, the IF code for Audit date has been removed. I simply want that IF code in Audit Date to always remain no matter how (A) is reponded.

 

[SkipVought]

When NO is chosen for Measurement no data is put in the Audit Date but the IF statement for the (B) cell is then gone.

THAT does not happen without the formula in column B actually being destroyed in some way!

In my sheet, for instance, I can change the value in column A all day long, and ABSOLUTELY NOTHING affects for formula. It is STILL in that row in column B, regardless of what happens in column A!!!

You have something else going on that you're not disclosing!

Skip,

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

 

[randy700]

But if (A) is NO then no data is put in the Audit Date (B) column and you can free type.

THIS looks like the culprit. If you allow users to type over your formula, ........


Randy

 

[SkipVought]

I assumed that the users entered formulas, as the OP stated, "users are going to fill out with a bunch of IF formulas"

Skip,

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

 

[Knicks]

I see what you are saying, they are typing over the formulas. Believe me I hate this program - I didn't design what they are trying to do. I am an Access guy, can usually make it do ANYTHING.

But they need to send these Excel spreadsheets out to field workers and have them enter data and what they are trying to do is create some sort of TEMPLATE perhaps?, where it auto fills some items depending upon (A) being YES but then they'd like to keep the rules for the next time usage while clearing out the entered data after saving/emailing back to main office for upload.

Anyone have any suggestions on work-around or fix. Excel to me is a tedious awful program for getting data but it is shockingly widely used so I have to get on the program. Uggg.

 

[SkipVought]

write some event driven VBA code to copy down from the cell where the change takes place thru each contiguous row that has 'Yes' in column A. It STOPS with the first cell that does not have 'Yes' in column A.

Is that the logic? What is the FIRST row that can be so changed to be made to propogate downward?

Skip,

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

 

[Knicks]

It propogates upward. It is driven off of the "A" column which effects columns "B" thru "M"......so if A5=YES column "B"="B4", A5=YES, "C5"="C4", and so on.

The goal is to copy or save that IF code in B-M regardless of whether A=YES or NO. I know that on NOs they are typing over the IF statemnt....is there some way can copy all that code so that it can be put back after they email the inputted data?

 

[SkipVought]

B-M is not upward!

If a change is made in B3, it affects the cells in column B from B4 DOWNWARD, based on the value in column A in any row.

The value entered in column B affects no values in other columns.

Or am I missing something?

Skip,

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

 

[Knicks]

Data in column B-M are initially dictated by whethe A=YES
IF A4= YES then B4=B3, A4=YES then C4=C3, A4=YES then D4=D3..... thru M
IF A5 = YES then B5=B4, A5=YES, then C5=C4, A5=YES, then D5=D4.... thru M

The IF code is in the B thru M fields.

 

[SkipVought]

So back to the post of 16:20 today.

Your response?

Skip,

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

 

[Knicks]

I"m thinking of trying a diff tactic with this.

I am trying to basically copy all the formulars originally and put in memory and then after sending email, clear contents and paste and then resave

On Activate sheet
Application.Range("a3:cj999").Copy


Then the button on the sheet,
ActiveWorkbook.Save
ActiveWorkbook.SendMail "charles.falk@nike.com", "Subject_line"

Application.Range("a3:cj999").Select
Application.Range("a3:cj999").ClearContents

Application.Range("a3:cj999").Select.PasteSpecial Paste:=xlPasteFormulas
Application.Range("a3:cj999").Select.PasteSpecial Paste:=xlPasteFormats


ActiveWorkbook.Save

I think some of this is working, the clear takes place but I am getting object error on the pasting of the formulas toward the end. I think this is what I want. Can you help on the copy/paste portion?

 

[SkipVought]

I'd use something like this where rData is the Named Range that you used to have your formulas and dates in. Paste in the Sheet Code Window (right click the sheet tab and select View Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lOff As Long
    
    If Target.Count > 1 Then Exit Sub
    
    With Target
        If Not Intersect(.Cells, [rData]) Is Nothing Then
            Do
                lOff = lOff + 1
                If LCase(Cells(.Row + lOff, "A").Value) = "yes" Then
                    With Cells(.Row + lOff, .Column)
                        .Value = .Offset(-1).Value
                    End With
                Else
                    Exit Do
                End If
            Loop
        End If
    End With
End Sub

okay then, your code

with ThisWorkbook
   .Save

   .SendMail "charles.falk@nike.com", "Subject_line"
end with

With ActiveSheet.Range("a3:cj999")
   .ClearContents
'you have already LOST the precious Copy so....
   YourFormulaRangeFromSomeOtherSheet.Copy

   .PasteSpecial Paste:=xlPasteFormulas
   .PasteSpecial Paste:=xlPasteFormats
end with

ActiveWorkbook.Save

Skip,

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

 

[randy700]

Are your users familiar with macros?
Can they be taught to use macros?

Create a "Master" worksheet (perhaps hidden) with all the formulas.
Build a macro that copies that worksheet to the "Data" worksheet.

Run the macro before beginning to enter new data.


Randy

 

[Knicks]

Shouldn't I be able to on activate 'copy' the range that contains the formulas and then eventually paste them back once the file has been emailed and contents cleared? I do a fair amount of VBA in Access, so the learning curve in Excel has been rough. The end user is going to be off-site technician so they won't be too adept.

1. upon opening of worksheet 'copy' range formulas to memory
2. let the user do entry
3. save
4. email
5. clear users cells
6. paste range of formulas back from step 1
7. save (back to original state when opened)

I am having trouble with step (6) and yet to confirm step (1), but I don't get an error until step (6).

Skip, I'd sort of prefer to leave the IF statements alone that are already there, as long as I can copy the formulas (and initially they won't have any data, just formulas) I'm happy as I've inherited their code and would prefer not to own that portion.

 

[SkipVought]

What about this process?
[tt]
1. upon opening of worksheet 'copy' range formulas to memory
2. let the user do entry
3. save
4. email
5. Close without saving
5. clear users cells
6. paste range of formulas back from step 1
7. save (back to original state when opened
[/tt]


Skip,

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

 

[mintjulep]

Why not just use a TEMPLATE, .XLT.


"Open" template. New workbook is created based on template. Do whatever. Template remains untouched, unchanged, safe.