Alternative trap for a sample INDEX function formula

=IF(ISNA(INDEX(whatever)),"",INDEX(whatever))


Gord Dibben MS Excel MVP


On Sun, 2 Mar 2008 10:02:01 -0800, Dave wrote:

It worked! Thanks!

Another format question: Can I format a cell to prevent it from displaying
"#N/A" when the reference cell does not yet contain any data? I've used the
INDEX function to retrieve a value from another worksheet.
DSM


"Rick Rothstein (MVP - VB)" wrote:

You can use this worksheet Change event code to do that. Right-click the
worksheet tab at the bottom and select View Code from the popup menu that
appears. You will taken to the VBA editor when you do this... copy/paste the
code below into the window that is displayed there...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B4")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
End If
Whoops:
Application.EnableEvents = True
End Sub

Now go back to the worksheet itself and type some characters into the range
of cells A1:B4... they will be automatically capitalized. Simply change the
Range statement in the If-Then statement to cover the cells you want this
functionality for.

Rick


"Dave" wrote in message
...
How do I format a cell to automatically CAPITALIZE a letter. E.G., I
enter
a1 in the cell, and I want it to convert to A1.
--
DSM