Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Offset Woes...

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a worksheet for which I want to select an area which is 11 columns wide by a variable # rows. This variable # of rows depends on whether there is data - the first row that lacks data in column k should be omitted from my selection. The first or anchor cell at top left of selection is a13. The longest the list could be is to row 300.

    My approach has been to attempt to define a name for this area - using this formula:
    =offset(indirect("a13"),0,0,max(1,count(indirect("k13:k300"))),11).

    This only selects the first row...what am I missing?

    I then want to sort this list by a column, paste all this on a new page, and insert subtotals by the sort key. I think I can get most of that, but any suggestions would be most helpful!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ever thought about using VBA?

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,593
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-16 13:29, Todd K wrote:
    I have a worksheet for which I want to select an area which is 11 columns wide by a variable # rows. This variable # of rows depends on whether there is data - the first row that lacks data in column k should be omitted from my selection. The first or anchor cell at top left of selection is a13. The longest the list could be is to row 300.

    My approach has been to attempt to define a name for this area - using this formula:
    =offset(indirect("a13"),0,0,max(1,count(indirect("k13:k300"))),11).

    This only selects the first row...what am I missing?

    I then want to sort this list by a column, paste all this on a new page, and insert subtotals by the sort key. I think I can get most of that, but any suggestions would be most helpful!
    Todd,

    I'll assume that the worksheet is named Data in what follows. Adjust to suit.

    Activate Insert|Name|Define.
    Enter Drecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Data!$K:$K)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter DRange as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$A$13,0,0,Drecs-ROW(Data!$A$13)+1,11)

    Activate OK.

    Now you can use Drange in formulas whenever appropriate to do so.

    Aladin

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 13:29, Todd K wrote:
    I have a worksheet for which I want to select an area which is 11 columns wide by a variable # rows. This variable # of rows depends on whether there is data - the first row that lacks data in column k should be omitted from my selection. The first or anchor cell at top left of selection is a13. The longest the list could be is to row 300.

    My approach has been to attempt to define a name for this area - using this formula:
    =offset(indirect("a13"),0,0,max(1,count(indirect("k13:k300"))),11).

    This only selects the first row...what am I missing?

    I then want to sort this list by a column, paste all this on a new page, and insert subtotals by the sort key. I think I can get most of that, but any suggestions would be most helpful!
    Hi ToddK:
    I think you are doing fine. I checked your setup and it works. If you are trying to select your range from your formula

    =OFFSET(INDIRECT("a13"),0,0,MAX(1,COUNT(INDIRECT("k13:k300"))),11)

    and you are selecting cell A13 only -- that's because you have hit a home run but you are on third base and have not completed the trip yet.

    After establishing your formula, this is what still needs to be done ...

    1) goto INSERT|NAME|DEFINE
    key-in TODDK (or whatever range name you want to assign in Names in Workbook

    2) in refers to, key in your formula

    =OFFSET(INDIRECT("a13"),0,0,MAX(1,COUNT(INDIRECT("k13:k300"))),11)

    3) OK

    now we are allset having established the range name that the formula will use

    Now if you go check with EDIT|GOTO|ToddK, you will see your selected range highlited

    HTH

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assuming that the value of A13 is I13 then the following formula should work:
    =OFFSET(INDIRECT(Sheet1!$A$13),0,0,MAX(1,COUNT(Sheet1!$K$13:$K$300)),11)

    It will select the rows I through S and the a certain amount of columns depending on the number of used cells in K13 through K300.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi and Al: Thanks for your input. Unfortunately, I still am unable to resolve the problems that I originally described, i.e., I still only get one row of data selected, even when there are 52 rows in the list. If you like, I can send you the workbook and maybe you will be able to troubleshoot what I am doing wrong, since I think this approach is still on the right track. Yes, Al, I am hoping to use some VBA to automate my tasks that I described in the original message, although I am certainly no wizard with VBA.

    Aladin - you are my white knight! It worked like a charm - and a very different approach from the one I have been laboring with. Please tell me that you make a living dealing with Excel - it will please me to know that some smart organization/people pay you for your brilliance!

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 18:55, Todd K wrote:
    Yogi and Al: Thanks for your input. Unfortunately, I still am unable to resolve the problems that I originally described, i.e., I still only get one row of data selected, even when there are 52 rows in the list. If you like, I can send you the workbook and maybe you will be able to troubleshoot what I am doing wrong, since I think this approach is still on the right track. Yes, Al, I am hoping to use some VBA to automate my tasks that I described in the original message, although I am certainly no wizard with VBA.

    Aladin - you are my white knight! It worked like a charm - and a very different approach from the one I have been laboring with. Please tell me that you make a living dealing with Excel - it will please me to know that some smart organization/people pay you for your brilliance!
    Hi ToddK:
    I don't know what you mean you still can select only one row even though there are 52 rows of data. I am able to select all rows starting from A13 all the way to K300 if I populate K13 to K300.
    Did you INSERT|DEFINE|NAME ToddK as I suggested and then EDIT|GOTO ToddK!
    All I can tell you I am able to select all the populated rows.
    So, please post back what happened when you inserted a range name and then selected the range by going to EDIT|GOTO ?

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi:

    I did exactly what you have indicated several times, but the only row which winds up selected is row 13, from columns A to K. It really has me stumped, cuz I too believe it should work, but it refuses to do so. Could there be an issue with the data type in column K? (I have text there...)

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah, I think I have figured out my problem. Can anyone help me twist these functions to deal with the fact that what I am really trying to do is limit the selection to the data that is in column B (which is text, not numbers) as opposed to the data in column K? Both approaches appear to work well when the data in K is numeric, but the function also allows the range to grow beyond the limits of where I want it because there are formulas in column K that appear to influence the depth of the selection, even when the formulas evaluate to zero. Column B, on the other hand, has text that will not go beyond the last and desired row...

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,593
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-17 07:02, Todd K wrote:
    Ah, I think I have figured out my problem. Can anyone help me twist these functions to deal with the fact that what I am really trying to do is limit the selection to the data that is in column B (which is text, not numbers) as opposed to the data in column K? Both approaches appear to work well when the data in K is numeric, but the function also allows the range to grow beyond the limits of where I want it because there are formulas in column K that appear to influence the depth of the selection, even when the formulas evaluate to zero. Column B, on the other hand, has text that will not go beyond the last and desired row...
    Just change my

    =MATCH(9.99999999999999E+307,Data!$K:$K)

    to either

    =MATCH(REPT("z",50),Data!$B:$B)

    or

    =MATCH(REPT("z",50),Data!$B$1:$B$300) [ in order to exploit the limit you mentioned ]

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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