Excel Help Needed Please.

[Jay]

Hi,

I have a set of Access Version numbers in a colum I need to convert into Access Version name

=IF(ISNUMBER(SEARCH("10*",L4)),"Access XP")

The above says if L4 has the number 10 with any numbers after then it is Access XP, what I can't work out is how I can then say if it is 11* its Access 2003 etc

I would normamly add a +then IF again but that doens't work at all.

Can anyone help here?

thanks

[scaryjim]

I think you need to nest IF statements. I quick check of Office help suggests that the syntax is IF(condition,true result,false result). If that's right, your statement should look like:

=IF(ISNUMBER(SEARCH("10*",L4)), "Access XP", IF(ISNUMBER(SEARCH("11*",L4)),"Access 2003"[, ... ]))

if the search finds "10*" it returns Access XP, if it doesn't it returns the result of the second IF statement. Then if you need to check for more versions you add another IF statement where I've put the square brackets.

[Jay]

That just errors with a #Value! error

[kompukare]

That just errors with a #Value! error

You can solve those by using Tools>Formula Auditing>Evaluate Formula and trace one part of the formula.

But actually in this case scaryjim's example has the 2nd nested IF's not true part listed as [] but to test with just two nested if you'd replace that with something like ,""
So:
=IF(A1="true1","true one",IF(A1="true2","true two",""))
Where the last bit is what you'd want if the 2nd IF is false: for example if you wanted a third IF there you'd go something like this:
=IF(A1="true1","true one",IF(A1="true2","true two",IF(A1="true3","true tree","")))

[Jay]

It was how I was closing the brackets

Incorrect

=IF(ISNUMBER(SEARCH("10*",L4)),"Access XP"), IF(ISNUMBER(SEARCH("11*",L4)),"Access 2003")

Correct

=IF(ISNUMBER(SEARCH("10*",L4)),"Access XP", IF(ISNUMBER(SEARCH("11*",L4)),"Access 2003"))

[grayg1]

I take it you want this to work all the way down the "L" column. I could probably create a program using VBA to do this but strangely I'm not very good with Excel formulas!

Edit: Oh well I see you solved it. For the hell of it I have written some code which would do it anyway, just needs adding to a button.

Dim varRowNumber As Integer

Sub Button1_Click()

varRowNumber = 1

Do Until Sheet1.Range("L" & varRowNumber) = ""
If Sheet1.Range("L" & varRowNumber) Like "7*" Then
Sheet1.Range("L" & varRowNumber) = "Access 95"
ElseIf Sheet1.Range("L" & varRowNumber) Like "8*" Then
Sheet1.Range("L" & varRowNumber) = "Access 97"
ElseIf Sheet1.Range("L" & varRowNumber) Like "9*" Then
Sheet1.Range("L" & varRowNumber) = "Access 2000"
ElseIf Sheet1.Range("L" & varRowNumber) Like "10*" Then
Sheet1.Range("L" & varRowNumber) = "Access XP"
ElseIf Sheet1.Range("L" & varRowNumber) Like "11*" Then
Sheet1.Range("L" & varRowNumber) = "Access 2003"
ElseIf Sheet1.Range("L" & varRowNumber) Like "12*" Then
Sheet1.Range("L" & varRowNumber) = "Access 2007"
ElseIf Sheet1.Range("L" & varRowNumber) Like "14*" Then
Sheet1.Range("L" & varRowNumber) = "Access 2010"
ElseIf Sheet1.Range("L" & varRowNumber) Like "15*" Then
Sheet1.Range("L" & varRowNumber) = "Access 2013"
End If
varRowNumber = varRowNumber + 1
Loop

End Sub

[scaryjim]

I take it you want this to work all the way down the "L" column. I could probably create a program using VBA to do this but strangely I'm not very good with Excel formulas!

You can use autofill once you've got it right on the first line, and it'll intelligently update the formula to look at the current line

If I knew more about the problem at hand I'd probably have suggested putting it into Access, but then I'm a database nerd, so of course I'd suggest that! We all have our own way of doing things - that's why tech and development teams have to be so closely managed and carefully handled...

[Chadders87]

I would have used a slightly different formula:

=IF(left(L4,2)=10,"Access XP", IF(LEFT(L4,2)=11,"Access 2003","Neither XP or Access"))

Both would work as well as each other though.