Validation?

S

seftonbarn

I need some help. For arguments sake my worksheet has only one column.
In row 1 is a Y/N field. If the user enters "N" in row 1 I want th
cell in row 2 to use a formula to sum a load of other totals in othe
worksheets. If row 1 is "Y" I want the user to be able to enter total
manually into row 2. I have achieved this rather crudely with
combination of a formula in row 2 that looks for the totals in th
other sheets and validation that prevents manual entry if row 1 i
"N".

This is OK unless having entered "Y" in row 1 and manually input dat
into row 2 the user does then not change his mind and set row 1 bac
to "N" expecting the formula to work. If this happens the formula i
lost in row 2. Any better ideas?? Thanks?
 
C

Cesar Zapata

hI,

I dont know if I really understand.


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("a1")) Is Nothing Then
Exit Sub
End If

If Range("a1").Value = "N" Then
Range("a2").Formula = "=b2+c2"
Else
Range("a2").Value = ""

End If

end sub


so if a1 is y then put the your formula in a2 if a1 is n then delete the
formula in a2.

Code needs to be in the sheet code. press Alt + f11 then double click on
your sheet and paste code,



Cesar Zapata
 
Top