+ Reply to Thread
Results 1 to 4 of 4

OFFSET/COUNTA merged cells

  1. #1
    Registered User
    Join Date
    11-02-2006
    Posts
    7

    OFFSET/COUNTA merged cells

    I have a bunch of data indicators arranged by Months, quarters and half years. The indicators run across the page and the quarters line up with the months (three cells merged) as do the half years (six cells merged). Named ranges on the monthly column works fine but when I try to use named ranges on the quarterly cells it gets really confusing. I will get the function to grab the last four in the row but when I enter another one the range doesn't move. =OFFSET(Sheet1!$A$1,0,COUNTA(Sheet1!$A$1:$AB$1),1,4). If I just enter the =COUNTA(Sheet1!$A$1:$AB$1) into a cell it will correctly return the number of cells(not the true number of cells that have been merged). Am I going to be able to do this with merged cells. I have a lot of work into the existing document so I would hate to start over. Thanks

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by dborchardt
    I have a bunch of data indicators arranged by Months, quarters and half years. The indicators run across the page and the quarters line up with the months (three cells merged) as do the half years (six cells merged). Named ranges on the monthly column works fine but when I try to use named ranges on the quarterly cells it gets really confusing. I will get the function to grab the last four in the row but when I enter another one the range doesn't move. =OFFSET(Sheet1!$A$1,0,COUNTA(Sheet1!$A$1:$AB$1),1,4). If I just enter the =COUNTA(Sheet1!$A$1:$AB$1) into a cell it will correctly return the number of cells(not the true number of cells that have been merged). Am I going to be able to do this with merged cells. I have a lot of work into the existing document so I would hate to start over. Thanks
    Hi,

    I believe that the Offset should work with cells even if merged, but note that if you merge cells 4 5 & 6 and then refer to Offset 4 5 & 6 you will get the result of cells 4 5 & 6 - ie, you will get the value from the merged cell followed by nothing and nothing (cell 4 is the active cell of the merge, cells 5 & 6 are deemed empty)

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    11-02-2006
    Posts
    7
    That helps. I am now seeing the pattern. Here is what seems to work:
    =OFFSET(Sheet1!$A$1,0,COUNTA(Sheet1!$A$1:$IP$1)*3-9,1,7)
    When I finially figured out that counta is only counting the cells that have someting in them and offset is counting all cells regarless of merged status, it all came together.Thanks

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by dborchardt
    That helps. I am now seeing the pattern. Here is what seems to work:
    =OFFSET(Sheet1!$A$1,0,COUNTA(Sheet1!$A$1:$IP$1)*3-9,1,7)
    When I finially figured out that counta is only counting the cells that have someting in them and offset is counting all cells regarless of merged status, it all came together.Thanks
    HI

    =CountA and =CountBlank should total the cells in the range, occupied or not.

    Good to see it worked for you, and thanks for the response.
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1