How to define a dynamic range?

H

hrithik

Active Member
Joined
Jul 26, 2010
Messages
336
I have a fixed range "Min" defined from $B$19 to $B26.

I want to make this range dynamic i.e starting at $B$19 but ends only on the last data cell in col B

I tried to use OFFSET(Data!$B$19,0,0,COUNTA(Data!$B:$B),1) and it does not work.

any suggestions?

Also, I need a formula for referencing the last cell in the range (for eg: $B$26 if the range is like in line1 of this post)​
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Hi,

Try this:
Code: 
=OFFSET(Data!$B$19,0,0,COUNTA(OFFSET(Data!$B$19,0,0):OFFSET(Data!$B$19,65000,0)),1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
206
Nick Loughton
N
SanjayGMusafir
  • Solved
Can we make reference to Named Ranges Dynamic?
Replies
1
Views
357
SanjayGMusafir
SanjayGMusafir
C
  • Question
Getting rows and columns from range object
Replies
1
Views
110
Cubist
Cubist
D
  • Solved
Is it possible to utilize the Min function with a dynamic range
Replies
7
Views
681
DonEB
D
S
  • Question
Dynamic dependent ComboBox VBA - error
Replies
3
Views
389
stemby
S

Forum statistics

Threads
1,215,064
Messages
6,122,941
Members
449,094
Latest member
teemeren

Share this page

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