Dynamic Named Range for a table? And not count entire column?

[svendiamond]

Hello there! I have two questions.

1. Can I make a dynamic named range for a table? Not just one column?

2. Can I make the "Count" part not count the whole column? Because I have a row of "totals" I don't want to include in the range.

I want to name the range from A1:O13 -- I want to name it "WORKRANGE" -- so when I type my formula for the named range what would I type?Here's what I'm thinking: "=OFFSET(A1:O1,0,0,COUNT(A2:A13)) -- I'm not counting the entire column because I don't want it to include my "TOTALS" row. Is this going to work?

I have Macro buttons just to the right of this screenshot. These buttons SORT by various columns for this table. Sometimes I have to go in and add a row in, so my range would become A1:O14 -- and if I add another it'd be A1:O15. I want my dynamic range to adapt to me adding rows in (e.g. between rows 6 and 7 I add a new row.) This way, I can refer to my dynamic named range in my VBA for those macro buttons, instead of going in and updating them all to include an extra few rows every time I change something.

Any help is appreciated! Thanks!

Here's a picture:

 

[ChrisM]

if you're always inserting rows, then there's really no need to count. It's always going to be A2 to however many rows you've inserted. Created a named range and set it equal to your cell "Totals/Averages". For example, you created a range called "LastRow" and its current value is "=A15". Then in your offset formula, instead of counting, just grab the row # of that range and subtract a row or two. You want 2 to 13 right? That's 11 rows, so it would be =ROW(LastRow) - 4

Again, this will only work if you are physically inserting rows, which will cause cell A15 to shift down one and become A16 etc

 

[svendiamond]

if you're always inserting rows, then there's really no need to count. It's always going to be A2 to however many rows you've inserted. Created a named range and set it equal to your cell "Totals/Averages". For example, you created a range called "LastRow" and its current value is "=A15". Then in your offset formula, instead of counting, just grab the row # of that range and subtract a row or two. You want 2 to 13 right? That's 11 rows, so it would be =ROW(LastRow) - 4

Again, this will only work if you are physically inserting rows, which will cause cell A15 to shift down one and become A16 etc

Thanks for the reply. I don't think I understand exactly what you're saying. If I want my range to include A1:O13 (for now) but extend if I add a row... it will automatically extend anyway? Is that what you're saying? If so, I still don't really understand how to create a Named Dynamic Range that covers more than just one column. Assuming I name the "Totals/Averages" cell "LastRow" -- should my named range formula be: "=OFFSET(A1:O1,0,0,LastRow-2)" or something like that?

 

[ChrisM]

Close!

=OFFSET(Sheet1!$A$2,0,0,ROW(LastRow)-1,15)

Note the -1, this is dependent on your starting row # (2 in my case). I set my sample range up as A2:A6. So ROW(LastRow) gives you 6. But you don't want your offset range to be six rows tall, you only want five rows (A2, A3, A4, A5, A6 = 5 rows), so I have to subtract one.

The the 15 gives you 15 columns, from A to O.

 

[svendiamond]

Close!

=OFFSET(Sheet1!$A$2,0,0,ROW(LastRow)-1,15)

Note the -1, this is dependent on your starting row # (2 in my case). I set my sample range up as A2:A6. So ROW(LastRow) gives you 6. But you don't want your offset range to be six rows tall, you only want five rows (A2, A3, A4, A5, A6 = 5 rows), so I have to subtract one.

The the 15 gives you 15 columns, from A to O.

Thank you very much sir! That makes a lot of sense. However, I realized I don't even need a dynamic range because the range will always extend when I add rows! Now I feel silly. I appreciate the knowledge anyway, though!