Offset Woes...

Todd K

New Member
Joined
Feb 21, 2002
Messages
31
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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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 ?
 
Upvote 0
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...)
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top