# Thread: Accessing a select statement from a cell (Excel 20

1. ## Re: Accessing a select statement from a cell (Excel 20

You could alos consider using the choose function.

2. ## Re: Accessing a select statement from a cell (Excel 20

Thanks but I don't understand how your reply is in reference to my question.

3. ## Accessing a select statement from a cell (Excel 20

edited by <!profile=unkamunka>unkamunka<!/profile> to prevent long formula distorting browser size

I have a number of cells in an Excel sheet that performs the following long If condition shown below looking up data in different tables on worksheets and this would be easier to maintain in a select statement. Right now the if condition is define in each cell but I am having a mental block about how to set up a marco containing a select statement doing the same thing and access it from the cell. How do I do this?

Thanks

=IF(E17=2000,VLOOKUP(MATCH(G17,Tab0),Table2000,(I1 7+3)),IF(E17=2001,VLOOKUP(MATCH(G17,Tab01),Table20 01,(I17+3)), _
IF(E17=2002,VLOOKUP(MATCH(G17,TAB2),Table2002,(I17 +3)), IF(E17=2003, IF(C17="T", VLOOKUP(MATCH(G17,Tab03T),Table2003T,(I17+3)), VLOOKUP(MATCH(G17,Tab03),Table2003,(I17+3))),IF(E1 7=2004, IF(C17="T", VLOOKUP(MATCH(G17,Tab04T),Table2004T,(I17+3)), _
VLOOKUP(MATCH(G17,Tab04),Table2004,(I17+3))),0) ))))

4. ## Re: Accessing a select statement from a cell (Excel 20

Do you mean this?

Public Function Bassett(rngCell As Range)
Dim rngTab As Range
Dim rngTable As Range
Select Case rngCell.Value
Case 2000
Set rngTab = Range("Tab0")
Case 2001
Set rngTab = Range("Tab01")
Case 2002
Set rngTab = Range("Tab2")
Case 2004
If rngCell.Offset(0, -2) = "T" Then
Set rngTab = Range("Tab03T")
Else
Set rngTab = Range("Tab03")
End If
Case 2004
If rngCell.Offset(0, -2) = "T" Then
Set rngTab = Range("Tab04T")
Else
Set rngTab = Range("Tab04")
End If
End Select
Set rngTable = Range("Table" & rngCell.Value)
Bassett = Application.WorksheetFunction.VLookup( _
Application.WorksheetFunction.Match( _
rngCell.Offset(0, 2), rngTab), rngTable, rngCell.Offset(0, 4) + 3, 0)
End Function

Use as

=Bassett(E17)

5. ## Re: Accessing a select statement from a cell (Excel 20

=CHOOSE(ARG, RETURN1, RETURN2, ETC.) is useful in some situations as a way around nested IFs. In your case Jan Karel saw that your conditions run in increments of 1 from a value of 2000 though 2004. Look up CHOOSE in the Help, the start of the function

=CHOOSE(E7-1999,

will return 1 for an E17 value of 2000, 2 for 2001, etc, and then you plug the respective functions in each return. This is untested because I didn't recreate your range names *, but when you get the parens right it should work:

=CHOOSE(E7-1999,VLOOKUP(MATCH(G17,Tab0),Table2000,(I17+3)),VL OOKUP(MATCH(G17,Tab01),Table2001,(I17+3)), _
,VLOOKUP(MATCH(G17,TAB2),Table2002,(I17+3)),IF(C17 ="T", VLOOKUP(MATCH(G17,Tab03T),Table2003T,(I17+3)), VLOOKUP(MATCH(G17,Tab03),Table2003,(I17+3)),IF(C17 ="T", VLOOKUP(MATCH(G17,Tab04T),Table2004T,(I17+3)), _
VLOOKUP(MATCH(G17,Tab04),Table2004,(I17+3)),0))

HTH.

* which is why it's useful to post a censored sample spreadsheet for a complex problem.

6. ## Re: Accessing a select statement from a cell (Excel 20

LOL...I think so. Thanks and I will let you know if this works.

7. ## Re: Accessing a select statement from a cell (Excel 20

I am getting an error dialog when typing to use the Application.WorksheetFunction.Match function, see the attachment.

8. ## Re: Accessing a select statement from a cell (Excel 20

We'd have to see the workbook to know why it happens.

9. ## Re: Accessing a select statement from a cell (Excel 20

Yea I understand and that is a problem to do as the worksheet has to do with CPA tasks plus its size is 552kb.

10. ## Re: Accessing a select statement from a cell (Excel 20

<P ID="edit" class=small>(Edited by sdckapr on 02-Aug-05 16:04. added PS)</P>If a match is not found you will get this error. Excel "ignores" the error and reports the error in the cell. You will have to trap the error in VB

Steve
PS
Something like this:

<pre>Dim lRow As Long
Dim vValue
Dim rList As Range

lRow = 0
On Error Resume Next
lRow = Application.WorksheetFunction.Match(vValue, rList, 0)
On Error GoTo 0
If lRow = 0 Then
MsgBox vValue & " is not found"
Else
'Your code here for a using the matched row
End If</pre>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•