Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Sacramento, California, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Nest Functions (Excel 2003)

    I am using this formula to determine the value in the cell above the cell matching the number 1 person. I get an error saying “The formula you typed contains an error.”
    =OFFSET(ADDRESS(6,(MATCH(1,G6:BK6,0)+6),4,FALSE),-1,0)
    When I use the formula without the nested Address or Match =OFFSET (S6,-1,0), I get the value I am looking for.
    The MATCH(1,G6:BK6,0) returns 13 – This is correct location of the number person in the range.
    The ADDRESS(6,(MATCH(1,G6:BK6,0)+6),4) returns S6 – This is the correct cell for the number 1 person on the worksheet

    However, =OFFSET(ADDRESS(6,(MATCH(1,G6:BK6,0)+6),4,FALSE),-1,0) comes up with the error above.

    Any help would be appreciated

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Nest Functions (Excel 2003)

    Wrap the ADDRESS function in the INDIRECT function:

    Since it's using the INDIRECT function, you'll have to change the FALSE to TRUE

    =OFFSET(INDIRECT(ADDRESS(6,(MATCH(1,G6:BK6,0)+6),4 ,TRUE)),-1,0)

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Nest Functions (Excel 2003)

    An alternate method would be to use the formula:

    =INDEX(G5:BK5,1,MATCH(1,G6:BK6))

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

    Re: Excel Nest Functions (Excel 2003)

    MBarron has already provided two solutions (I like the second one best).

    The reason that your formula doesn't work is that ADDRESS returns a string "S6", not a reference to cell S6.

Posting Permissions

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