Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with Offset

    Hi Loungers, after some assistance again. I have this formula that works fine

    =OFFSET('Regional Documents'!$K$7,0,0,COUNTA('Regional Documents'!$K$7:$K$300),1)

    The issue I'm having is that Colum k contains =IF(C7="",""....., so even though the cells on Colum k are "empty" the offset formula counts the cell with the formula so the list expands.

    Is there a way to modify the Offset formula to count and then offset only those cells in K that contain a value?

    I hope this makes since - any assistance would be very much appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The cells are not really empty, They do contain a null string, so it is not surprising that they are counted. You could base the offset on column C if that is the one which has blanks:
    =OFFSET('Regional Documents'!$K$7,0,0,COUNTA('Regional Documents'!$C$7:$C$300),1)

    You could also use something like:
    =OFFSET('Regional Documents'!$K$7,0,0,COUNTIF('Regional Documents'!$K$7:$K$300,">"""),1)

    Both assume that the blanks/null strings are all at the end of the data. If the blanks/ null strings are within the dataset, OFFSET will not work, unless you count them as well...


    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Steve

    Re: "The cells are not really empty, They do contain a null string.."

    ..the cells actually contain a formula.
    ..but might display as empty

    COUNTA will always count cells that have formulas, irregardless* of what they display.

    Verada, perhaps you should let us know what you are trying to do. There will probably be another way of doing it.

    zeddy
    * I made that word up

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Zeddy you are correct they contain a formula, not a null string. But they do NOT "display as empty", they display as a null string which is a zero-length string. If you convert the cell contents to a value, the display will not change, but it will still not be empty, it will be a string, albeit a string which has no length, and this is still different than being empty...

    Steve
    PS it is akin to asking a chemist if a glass containing some water is half-full or half-empty. It is neither. The glass is completely full: some of the contents are liquid (the water) and some are a gas (the air components).

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    The question here is how full of gas are you! Sorry, I just couldn't resist. Bad RG Bad RG!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    When people say that I am full of it, I don't think they are referring to gas, but more some kind of solid waste.

    Steve

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Steve

    I said "display as empty" for XPDNC.
    I couldn't bring myself to say "display as a null string" (because what does that look like)
    Anyway, you are right. Who am I to disagree. (Sweet dreams are made of cheese). Blimey, you got me singing now.

    And if you ask me whether a glass is half-full or half-empty, my normal reply is "it's not big enough".
    RG: It's hard to catch Steve out. The stuffy nose. I mean, the stuff he knows. His help and advice is always valuable, mine is invaluable.

    zeddy
    PS On a sanitation truck in the Caribbean, it had on the back
    "On the road to suck cess"
    Last edited by zeddy; 2013-08-29 at 17:21.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks all for your assistance again.
    I tried Steve's solution #2 and that looks to do the job - Thanks Steve.

    The Lounge has done it again!

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I said "display as empty" for XPDNC.
    I couldn't bring myself to say "display as a null string" (because what does that look like)
    This is a little "nit-picky" but empty and blank, in excel and excel VBA have a particular meaning. A null string will give TRUE for ISTEXT, but FALSE for ISBLANK and ISEMPTY. An empty cell will be false for ISTEXT but TRUE for ISBLANK and ISEMPTY. And the whole question arose since COUNTA will count text, but will not count empty/blank cells. Spaces in a cell can look blank as trailing spaces in text strings, but the lack of the appearance is what can cause the issues when doing comparisons or counting the length, etc

    It's hard to catch Steve out. The stuffy nose.
    It is not "stuffy", I am just genetically pre-disposed to having a large nose...

    Steve

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Steve

    We both know the complexities of null, blank, spaces, empty etc in Excel.
    COUNTA will not count empty cells. So, if a cell has any formula in it, ergo, it is not empty.
    So my point was to let others know you should never use COUNTA if your range includes cells containing formulas.
    ..you should use COUNTIF instead
    ..as you pointed out.

    zeddy

  11. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Loungers

    After some advise similar to above, I have a dynamic print range
    =OFFSET('Child Doc Register'!$C$3,0,0,COUNTif('Child Doc Register'!$C$8:$C$2000)+5,COUNTA('Child Doc Register'!$C$8:$I$8))
    which works fine, but similar problem to above it refers to a Child Doc Register that contains cell references to another work sheet, some of which are blank.
    Tried to adapt the solution above -
    =OFFSET('Child Doc Register'!$C$3,0,0,COUNTif('Child Doc Register'!$C$8:$C$2000)+5,">"""),1,COUNTA('Child Doc Register'!$C$8:$I$8)), but getting errors.

    Any thoughts would be greatly appreciated.

    Regards

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Perhaps you meant:

    =OFFSET('Child Doc Register'!$C$3,0,0,COUNTif('Child Doc Register'!$C$8:$C$2000,">""")+5,1,COUNTA('Child Doc Register'!$C$8:$I$8))
    Regards,
    Rory

    Microsoft MVP - Excel

  13. The Following User Says Thank You to rory For This Useful Post:

    verada (2013-09-20)

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    ..or maybe, instead of adding 5, you just changed the start point from $C$3 to $C$8 as in..

    =OFFSET('Child Doc Register'!$C$8,0,0,COUNTif('Child Doc Register'!$C$8:$C$2000,">"""),1,COUNTA('Child Doc Register'!$C$8:$I$8))

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2013-09-20)

Posting Permissions

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