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



"Mac" <Mac@discussions.microsoft.com> wrote in message 
news:D23BFF5C-DAD8-439A-BD27-81EA43913C36@microsoft.com...
> 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 

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

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.programming&cat=en_US_3a793e1f-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


-- 
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what 
is helpful. 


"Mac" wrote:

> 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