Need Excel Formula

  • Thread starter Thread starter Mac
  • Start date Start date
M

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
 
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
 
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
 
Back
Top