View Poll Results: Did I provide an appropriate amount of information to clearly present the problem?

Voters
0. This poll is closed

  • Yes

    0 0%

  • No

    0 0%

  • too little

    0 0%

  • too much

    0 0%
+ Reply to Thread
Results 1 to 7 of 7

VLOOKUP problem from multiple sheets

Hybrid View

  1. 05-13-2011, 01:31 PM #1
    boydcarter
    boydcarter is offline
    Registered User
    Join Date
    05-12-2011
    Location
    toronto, Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Thumbs up VLOOKUP problem from multiple sheets


    On an Open sheet where the remaining sheets are named sheet2 and sheet3. I entered in d2, a general-formatted cell: =VLOOKUP(c2,’*’!a:b,2,0). The formula was accepted and excel converted the formula to =VLOOKUP(C2,Sheet2:Sheet3!A:B,2,0) and returned an error code of #VALUE. (Col c on this sheet is formatted for text.) If I “step in” when the evaluation underlines c2, I get the message: The cell currently being evaluated contains a constant. This was addressed at: http://www.excelforum.com/excel-work...-constant.html but didn’t solve my problem. I’ve attached a file representing the problem. I’ve struggled with this over a day, visiting many forums where the question seems to be answered, to no avail. Can anyone tell me what I’m doing wrong? Thanks. Boyd Carter
    Attached Files Attached Files
    Last edited by boydcarter; 05-14-2011 at 06:59 AM. Reason: To close the thread.
    Reply With QuoteRegister To Reply
  2. 05-13-2011, 08:41 PM #2
    boydcarter
    boydcarter is offline
    Registered User
    Join Date
    05-12-2011
    Location
    toronto, Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: VLOOKUP problem from multiple sheets

    Continued: I have determined that the problem is with the multi-sheet table array. The formula works ok if I use a range on the same page. Don't know why the underline is on the lookup value. Boyd
    Reply With QuoteRegister To Reply
  3. 05-14-2011, 01:50 AM #3
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: VLOOKUP problem from multiple sheets

    As you infer VLOOKUP does not work in 3D - you would need to identify a single sheet range, e.g:

    Sheet1!$D2
=VLOOKUP($C2,IF(ISNA(MATCH($C2,Sheet2!$A:$A,0)),Sheet3!$A:$B,Sheet2!$A:$B),2,0)
copied down
    The above represents just one of a myriad of possibilities. Depending on the number of sheets you have to check you may find you are best served reverting to a User Defined Function (VBA)

    On a final note - please do not add Polls to your threads unless relevant to the question at hand.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  4. 05-14-2011, 06:47 AM #4
    boydcarter
    boydcarter is offline
    Registered User
    Join Date
    05-12-2011
    Location
    toronto, Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Thumbs up Re: VLOOKUP problem from multiple sheets

    Thank you, DonkeyOte, your reply is the best I've found in two days of searching. Obviously I'm not very competent in this area. Sorry I misunderstood the intent of the survey; I'll do better next time. Thanks again, Boyd
    Reply With QuoteRegister To Reply
  5. 05-25-2011, 03:39 PM #5
    rhysspinner
    rhysspinner is offline
    Registered User
    Join Date
    05-02-2011
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: VLOOKUP problem from multiple sheets

    this is exactly the problem I am trying to solve and the above formula works really well but I am not sure of the syntax to extend the formula to cover more than the two sheets. any help on that would be really appreciated but I have about 40 sheets so it might get a bit complicated. I saw on another post the following code which seemed to work except for two problems

    1) it wouldn't automatically recalculate. ie if the lookup result changed it would still show the old result unless I hit ctrl+alt+F9 and

    2) I don't know how to change it to search for text. I want to refer to the cell and have the vlookup go through the rest of the workbook to find the next instance of that text and take the value in the column next to it. The text will change each day so I can't hard code it in. any ideas ????
    Cheers
    Rhys

    Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
    Col_num As Integer, Optional Range_look As Boolean)
    Dim wSheet As Worksheet
    Dim vFound
    On Error Resume Next
    For Each wSheet In Workbooks(Tble_Array.Parent.Parent.Name).Sheets
    With wSheet
    Set Tble_Array = .Range(Tble_Array.Address)
    vFound = WorksheetFunction.VLookup _
    (Look_Value, Tble_Array, _
    Col_num, Range_look)
    End With
    If Not IsEmpty(vFound) Then Exit For
    Next wSheet
    Set Tble_Array = Nothing
    VLOOKAllSheets = vFound
    End Function
    Reply With QuoteRegister To Reply
  6. 11-13-2013, 09:44 AM #6
    lyzetteb
    lyzetteb is offline
    Registered User
    Join Date
    11-13-2013
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: VLOOKUP the cell currently being evaluated is a constant

    Hi, am running a vlookup and I know the data should match but keep getting the 'the cell currently being evaluated is a constant' .Have done all the normal checks, data is correctly sorted, cleaned and trimmed all the data and ensure it set to 'general' - just cannot get the vlookup to work. Have also copied and pasted into new workbook... please help. Data attached.
    Attached Files Attached Files
    Last edited by lyzetteb; 11-13-2013 at 09:59 AM.
    Reply With QuoteRegister To Reply
  7. 11-14-2013, 06:20 AM #7
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: VLOOKUP problem from multiple sheets

    lyzetteb,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1