Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing a select statement from a cell (Excel 20

    You could alos consider using the choose function.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing a select statement from a cell (Excel 20

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

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •