Need Excel Formula

[Mac]

Hi. Using Excel 2007. I want to force certain information into a cell.
Example: If Column B contains the text: "SAI", then column C must only allow
for a 13 digit long number (numeric). If I capture anything short of 13
digits into Column C, it must not allow this and force an error. However, if
Column B contains the text: "Passport", then column C must default to an
alphanumeric field (because passport no's differ in length and can be
alphanumeric).

I am not sure if Excel can cater for this in it's normal functions? Please
can someone assit?

Thanks

 

[Scott M.]

This doesn't pertain to Microsoft .NET programming (which is what this group
is for), but I can tell you that to do that sort of complex If/Then logic,
you would need to program your requirements using Visual Basic for
Applications (VBA) in your Excel spreadsheet. In that environment, what you
want could be accomplished easily.

-Scott

 

[John Bundy]

As Scott mentioned the Excel programming group is the place to be:

http://www.microsoft.com/communitie...e1f-4961-419d-9ec7-899d6e6086cd&lang=en&cr=US

This may get you started, place this in the sheet you want it to work on:

Dim prevCell As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRow, myCol As Integer
myRow = Target.Row
myCol = Target.Column
If prevCell <> 0 Then
If Target.Column = 3 Then 'the third column is C
If UCase(Cells(prevCell, 2).Text) = "SAI" Then
If Len(Cells(prevCell, myCol)) > 13 Then
MsgBox "Cell must contain no more that 13 digits", vbCritical
Cells(prevCell, 3).Clear
End If
End If
End If
End If
prevCell = Target.Row
End Sub