Results 1 to 4 of 4
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm using the Offset formula and I would like to indicate the cell addresses referred to in the formula.
    I'm having the feeling that the answer is obvious and I'm missing it. Lunch must be soon.

    So if the offset formula is: =OFFSET(A1,1,0,2,1)
    I'd like to know that it is referring to A2:A3
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Well even lunch didn't help me remember how to extract cell addresses. But I did come up with a different approach using the Cell function.
    =AND(CELL("address",A3)>=CELL("address",OFFSET($A$ 1,1,0,$C$2,1)),
    CELL("address",A3)<=CELL("address",OFFSET($A$1,$C$ 2,0,1,1)))

    I suppose I should step back here and mention that the ultimate goal was to highlight (using conditional formatting) the cells referenced in an offset formula). Above is the formula I used in the conditional formatting.

    Here is a rough example - as you change the height of the offset range, the highlighting adapts.[attachment=91225:Highlighted Offset formula.xlsx]
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Whoops I posted the above example too soon - doesn't actually function
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    After reading this blog post I came up with a different approach. [attachment=91227:Highlighted Offset formula.xlsx]
    Note the space before A2 in this formula
    =TYPE((OFFSET($A$1,$F$2,$G$2,$H$2,$I$2) A2))<>16
    When I tested the formula in the conditional formatting dialog, I found that intersections are specifically prohibited. So I made the formula into a Named range and referenced in the conditional format dialog.

    whoo hooo!
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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