Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't use ADDRESS within an OFFSET (2003/SP-2)

    I'm trying to have an OFFSET formua, and I have it all working except for one thing. I cannot get OFFSET(ADDRESS(16, 9), 1,1, 1, 1) to work. It comes up as an error. I can do OFFSET(I16, 1,1, 1, 1) just fine.

    The reason I am trying to use ADDRESS is that my range is very dynamic. I won't be focusing on a specific cell. Using MATCH, I can represent the cell I want using ADDRESS. But it seems OFFSET doesn't like using ADDRESS as the reference.

    Is there something I can use in place of ADDRESS?

    Edit: Oh, and the reference is to a cell on another sheet, which is handled pretty nicely by the fifth argument of ADDRESS. Now if I can just get it to play nice with OFFSET.

    Kevin

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

    Re: Can't use ADDRESS within an OFFSET (2003/SP-2)

    The result of ADDRESS is a string (text value), not a cell reference. For example, the result of ADDRESS(4,3) is the string "C4", not a reference to cell C4. You can use INDIRECT to create a cell reference from this string:

    =OFFSET(INDIRECT(ADDRESS(16,9)),1,1,1,1)

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

    Re: Can't use ADDRESS within an OFFSET (2003/SP-2)

    BTW, if you're computing the row and column number, you don't really need ADDRESS - you can use A1 as a fixed base and use the rows and cols arguments of OFFSET, e.g.

    =OFFSET(OtherSheet!$A$1,P1,Q1)

    P1 on the current sheet contains the row offset, and Q1 the column offset. You can use expressions too, of course:

    =OFFSET(OtherSheet!$A$1,2*P1-1,MATCH(Q1,A1:A10,0)-1)

  4. #4
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use ADDRESS within an OFFSET (2003/SP-2)

    Well, now I feel the fool.

    I saw this solution elsewhere with my Google Fu, but it didn't work for me. When I saw your post, I took a closer look at what I did. You're quite correct; INDIRECT did the trick. I just had the parentheses wrong in my formula. Thanks for straightening that out for me. Knowing how unreliable random web pages can be, I just assumed the solution was wrong rather than look that carefully at my work.

    Kevin

Posting Permissions

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