Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Range Reference - Part 2 (2003)

    1) I want to look up a value on the ‘TC Scenario’ screen (range TC_SCENARIO) cell B3.

    2) Take that value and go to screen ‘Data Feed AT’ (range DATAFEED), search across row #1 for the cell containing the value from ‘TC Scenario”!B3.


    3) Once I locate the cell containing the value from ‘TC Scenario”!B3, go down that row and return the value from the same row that the formula is sitting in.


    I created the following formula, but it is not returning the correct data from the Data Feed sheet. : =T(HLOOKUP(('TC Scenario'!B3),DataFeed,(ROW())))

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range Reference - Part 2 (2003)

    Does it help if you add FALSE as fourth parameter to HLOOKUP?

    =T(HLOOKUP('TC Scenario'!B3,DataFeed,ROW(),FALSE))

    If not, could you attach (a stripped down copy of) the workbook?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Range Reference - Part 2 (2003)

    It didn't work ~sigh

    Attached is a copy of the workbook
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range Reference - Part 2 (2003)

    The definition of the named range DataFeed is incorrect - it should include row 1 since that is the row in which you want to look up a value. So it should be defined as (for example)

    ='DATA FEED AT'!$A$1:$F$17

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range Reference - Part 2 (2003)

    And to make the formula work correctly, you need to add 1 to the row number:

    =HLOOKUP('TC Scenario'!B$3,DataFeed,ROW()+1,FALSE)

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Range Reference - Part 2 (2003)

    Hans, that is beautiful!

    The Data Feed A1 ... reference makes sense (of course it does... it works).

    I noticed that I didn't need the "T" before the HLOOKUP?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range Reference - Part 2 (2003)

    I'm not sure why you used T - it's only needed if you want to convert a result to text.

Posting Permissions

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