Validation?

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 the
cell in row 2 to use a formula to sum a load of other totals in other
worksheets. If row 1 is "Y" I want the user to be able to enter totals
manually into row 2. I have achieved this rather crudely with a
combination of a formula in row 2 that looks for the totals in the
other sheets and validation that prevents manual entry if row 1 is
"N".

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


---
Message posted from http://www.ExcelForum.com/

Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany


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 the cell in row 2 to use a formula to sum a load of other totals
in other worksheets. If row 1 is "Y" I want the user to be able to
enter totals manually into row 2. I have achieved this rather
crudely with a combination of a formula in row 2 that looks for the
totals in the other sheets and validation that prevents manual entry
if row 1 is "N".

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


---
Message posted from http://www.ExcelForum.com/

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



seftonbarn wrote:

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 the
cell in row 2 to use a formula to sum a load of other totals in other
worksheets. If row 1 is "Y" I want the user to be able to enter totals
manually into row 2. I have achieved this rather crudely with a
combination of a formula in row 2 that looks for the totals in the
other sheets and validation that prevents manual entry if row 1 is
"N".

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


---
Message posted from http://www.ExcelForum.com/