+ Reply to Thread
Results 1 to 14 of 14

Problem with Indirect function in HLOOKUP

  1. #1
    Registered User
    Join Date
    07-24-2012
    Location
    United States
    MS-Off Ver
    Excel 2002 SP3
    Posts
    7

    Angry Problem with Indirect function in HLOOKUP

    I can get two formulas to correctly display a value, but when I try to combine the formulas for simplicity I get a "The formula you typed contains an error" error message. The "incorrect" formula (two combined) is:

    =HLOOKUP(INDIRECT("'Eq. List-to-P&ID template'!"&CHAR(65+MATCH(LEFT(IF(NOT(LEFT(RIGHT(E3,6),1)="-"),RIGHT(E3,6),RIGHT(E3,5)),2)&"xxx",'Eq. List-to-P&ID template'!$B28:$K28,0))&"30"),'Eq List 5.11.12'!$A$2:$CV$598,MATCH(E3,'Eq List 5.11.12'!$A2:$A598,0),0)

    , and it should display "406 GAL", but I get the error message.

    The formula broken into two chunks works:

    =INDIRECT("'Eq. List-to-P&ID template'!"&CHAR(65+MATCH(LEFT(IF(NOT(LEFT(RIGHT(E3,6),1)="-"),RIGHT(E3,6),RIGHT(E3,5)),2)&"xxx",'Eq. List-to-P&ID template'!$B28:$K28,0))&"30") gives me "Capacity" in cell E34, then

    =HLOOKUP(E34,'Eq List 5.11.12'!$A$2:$CV$598,MATCH(E3,'Eq List 5.11.12'!$A2:$A598,0),0) displays "406 GAL" as it should.

    So the problem is with combining the two. When I replace the INDIRECT(...) statement in for E34 in the HLOOKUP, I get the error statement. I know this is difficult to track a bug without having the spreadsheet, but does anybody know why this might be happening? All I'm doing is replacing values that already work!

    Thanks guys!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Problem with Indirect function in HLOOKUP

    This worked for me:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-24-2012
    Location
    United States
    MS-Off Ver
    Excel 2002 SP3
    Posts
    7

    Re: Problem with Indirect function in HLOOKUP

    NBVC,

    Your formula contains '[Eq List 5.11.12]Eq List 5.11'! at some places, which happens when Excel can't find the sheet you're looking for (I'm sure you knew that). Either way, when I cleaned that up and threw it back into Excel, it was the exact same code that I had and it still gave me the error. Do you think it might be a software version issue?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Problem with Indirect function in HLOOKUP

    I cleaned those out and it still did not error out... as in no pop up error message boxes...

    Please Login or Register  to view this content.
    well, I get #N/A but I have no data...
    Last edited by NBVC; 07-24-2012 at 04:07 PM.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Problem with Indirect function in HLOOKUP

    why don't you post a sample workbook?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    07-24-2012
    Location
    United States
    MS-Off Ver
    Excel 2002 SP3
    Posts
    7

    Re: Problem with Indirect function in HLOOKUP

    Sample of Problem.xls

    Check out the attachment. Let me know if you get the error or not

  7. #7
    Registered User
    Join Date
    07-24-2012
    Location
    United States
    MS-Off Ver
    Excel 2002 SP3
    Posts
    7

    Re: Problem with Indirect function in HLOOKUP

    It's a very dumbed down version of the workbook for confidentiality reasons, but it still gives the same headache.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Problem with Indirect function in HLOOKUP

    Yes, the error you are getting is because you are only allowed up to 7 nested functions in Excel (prior to Excel 2007), doing that extra nesting takes you over the limit....

  9. #9
    Registered User
    Join Date
    07-24-2012
    Location
    United States
    MS-Off Ver
    Excel 2002 SP3
    Posts
    7

    Re: Problem with Indirect function in HLOOKUP

    UGHHHHHH, that's what I feared. Good to know work cares enough about the future to keep our software in the past. I appreciate the quick responses to keep me from losing it! I'll just have to go two-celled I suppose. Thanks everybody!

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Problem with Indirect function in HLOOKUP

    One minor change in your formula, makes it work....

    Please Login or Register  to view this content.


    ---------- Post added at 04:30 PM ---------- Previous post was at 04:29 PM ----------

    I changed NOT(LEFT(RIGHT(C3,6),1)="-") to LEFT(RIGHT(C3,6),1)<>"-" ... this loses just enough nested functions...

  11. #11
    Registered User
    Join Date
    07-24-2012
    Location
    United States
    MS-Off Ver
    Excel 2002 SP3
    Posts
    7

    Re: Problem with Indirect function in HLOOKUP

    THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!! Something so simple. Even if I had known the nest rule, I wouldn't have caught that. A million thanks, NBVC!

  12. #12
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Problem with Indirect function in HLOOKUP

    @ lilaustino

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

  13. #13
    Registered User
    Join Date
    07-24-2012
    Location
    United States
    MS-Off Ver
    Excel 2002 SP3
    Posts
    7

    Re: Problem with Indirect function in HLOOKUP

    Sorry about that, Cutter. I'm a rook, and I won't make the mistake again. This forum was very useful, so I'll def be back with more questions. Thanks again!

  14. #14
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Problem with Indirect function in HLOOKUP

    what a large black bird that nests in groups?

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1