Offset Woes...

[Todd K]

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!

[Al Chara]

Ever thought about using VBA?

[Aladin Akyurek]

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

[Yogi Anand]

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

[Al Chara]

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.

[Todd K]

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!

[Yogi Anand]

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 ?

[Todd K]

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...)

[Todd K]

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...

[Aladin Akyurek]

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