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

    INDIRECT combined with HLOOKUP (2003)

    I was having troubles with formulas breaking when I inserted a row into my data sheet. So, with a litte research on this site, I found that what I need to use is the INDIRECT command.

    The original formula: =HLOOKUP('TC Scenario'!B$3, 'DATA FEED AT'!$A$1:$BN$400,ROW(),FALSE)
    combined with the INDIRECT command...

    I created: =INDIRECT(HLOOKUP('TC Scenario'!B$3,'DATA FEED AT'!$A$1:$BJ$400,ROW(),FALSE))

    Which got me close. When I ran "evaluate formula", everything was fine until the final combination: INDIRECT("<font face="EraserDust">VALUE</font face=dust> "). When it combined further, I got a #REF

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

    Re: INDIRECT combined with HLOOKUP (2003)

    The argument to INDIRECT must be a text string that represents a cell range, not a formula. Try this:
    <code>
    =HLOOKUP('TC Scenario'!B$3,INDIRECT("'DATA FEED AT'!$A$1:$BN$400"),ROW(),FALSE)
    </code>
    if you want to "fix" the range in which you do the lookup, or
    <code>
    =HLOOKUP(INDIRECT("'TC Scenario'!B$3"),'DATA FEED AT'!$A$1:$BN$400,ROW(),FALSE)
    </code>
    if you want to "fix" the cell containing the lookup value.

  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: INDIRECT combined with HLOOKUP (2003)

    Thank you! It worked! (Both worked)

    I was looking for a fixed range.

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

    Re: INDIRECT combined with HLOOKUP (2003)

    I have to recant... it partially worked.

    Perhaps I was even asking for the incorrect thing.

    What I _want_ is to insert 3 rows between the current rows 3 & 4 (row 4 becomes row 7).
    When I do this, I don't want my formulas to break. I'm _hoping_ for
    =HLOOKUP(INDIRECT("'TC Scenario'!B$3"),'DATA FEED AT'!$A$1:$BN$400,ROW() +1 ,FALSE)

    to become


    =HLOOKUP(INDIRECT("'TC Scenario'!B$3"),'DATA FEED AT'!$A$1:$BN$400,ROW() -2 ,FALSE)

    withouth me fat-fingering each formula.

    See attachment New Case screen Row 4
    Attached Files Attached Files

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

    Re: INDIRECT combined with HLOOKUP (2003)

    Instead of HLOOKUP, you can use INDEX with two MATCH formulas, one for the column lookup and the other for the row lookup. In E4:

    =INDEX('DATA FEED AT'!$D$3:$F$9,MATCH($D4,'DATA FEED AT'!$B$3:$B$9,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$F$1,0))

    This formula can be copied to the other cells.

    (The ranges reflect those in your sample workbook, they'd have to be expanded for the real workbook.)

    See attached version.
    Attached Files Attached Files

  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: INDIRECT combined with HLOOKUP (2003)

    The indexing seems to work well!


    In the sample provided, I increased the range to include another cell that I am trying to concatenate.

    I want to combine rows 20/21 on the data sheet to 1 field on the New Case screen. The code that I currently have in the column is the original HLookup formula.

    If it isn't possible, I could live with updating those formulas manually if rows/columns are shifted, as there are only about 15 total in the whole project.... but it sure would be nice!
    Attached Files Attached Files

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

    Re: INDIRECT combined with HLOOKUP (2003)

    You could use this in E21:

    =INDEX('DATA FEED AT'!$D$3:$F$25,MATCH("Age",'DATA FEED AT'!$B$3:$B$25,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$H$1,0))&" "&INDEX('DATA FEED AT'!$D$3:$F$25,MATCH("Unit",'DATA FEED AT'!$B$3:$B$25,0),MATCH('TC Scenario'!B$3,'DATA FEED AT'!$D$1:$H$1,0))

    It uses literal search strings "Age" and "Unit" instead of cell references.

Posting Permissions

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