simple one

I

icestationzbra

hi,

i have a rather simple query, but i am not able to figure out how to d
the required thing.

i have two columns. lets say, i enter 'N/A' in A1, then B1 shoul
display '0'. if i enter anything else in A1, excel should allow me t
enter whatever else i want to in this cell.

i tried entering an 'if' formula in B1 such that when i enter 'N/A' i
A1, B1 displays 0. this works fine as long as i do not enter anythin
in B1. when i enter something other than "N/A" in A1 and i enter som
other value (other than 0) in B1, the formula gets erased.

i tried data validation, but it does not display the '0' in B1 when
enter 'N/A' in A1.

please help.

mac
 
N

Norman Harker

Hi Mac!

What you are trying to do can't be done by formulas and requires VBA.

See:
The details of capabilities of functions and formulas are based on:

170787 XL: Custom Functions Can't Change Microsoft Excel Environment
http://support.microsoft.com/default.aspx?scid=kb;en-us;170787

A Function or User Defined Function called by a formula in a worksheet
cell cannot change the environment of Microsoft Excel. This means that
such a function cannot do any of the following:

Ø Insert, delete, or format cells on the spreadsheet.
Ø Change another cell's value.
Ø Move, rename, delete, or add sheets to a workbook.
Ø Change any of the environment options, such as calculation
mode or screen views.
Ø Add names to a workbook.
Ø Set properties or execute most methods.
 
A

Andy Brown

i tried entering an 'if' formula in B1 such that when i enter 'N/A' in
A1, B1 displays 0. this works fine as long as i do not enter anything
in B1. when i enter something other than "N/A" in A1 and i enter some
other value (other than 0) in B1, the formula gets erased.
Click to expand...
i tried data validation, but it does not display the '0' in B1 when i
enter 'N/A' in A1.
Click to expand...

There's no such thing as conditional texting. I believe you need code.
Rightclick the sheet tab and "View Code". Paste in the following -

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Value = "N/A" Then
Target.Range("B1").Value = 0
End If
End Sub

Close the Visual Basic Editor, save the workbook, test.

HTH,
Andy
 
Top