Horizontal dynamic range using Offset not stopping at correct place

bluefish44

Board Regular
Joined
Apr 3, 2009
Messages
187
currently I am using this formula:
=OFFSET(CHARTMAKER!F4,0,0,1,COUNTA(CHARTMAKER!4:4)-1)

the last 3 cells of data in my range are a formula that returns "", so the cells look blank.
I would like the formula to stop at the cells that contain the "" but the formula doesn't recognize these cells as blank and includes them in the range.

Any way around this?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Thanks JoeMo - but this doesnt see to work, it still extends past the last three cells that contain the ""
This is the formula I used
OFFSET(CHARTMAKER!f4,0,0,1,COUNTIF(CHARTMAKER!4:4,"<>")-1)
 
Upvote 0
Thanks JoeMo - but this doesnt see to work, it still extends past the last three cells that contain the ""
This is the formula I used
OFFSET(CHARTMAKER!f4,0,0,1,COUNTIF(CHARTMAKER!4:4,"<>")-1)
I realized that and deleted my post, but guess you got to it before the deletion. Sorry for that. If you can limit your range so as to not include truly empty cells (blank cells w/o formulas) then you could use COUNTA(yourRange)-COUNTBLANK(your Range). COUNTBLANK counts cells with formulas returning "", but it also counts truly empty cells.
 
Last edited:
Upvote 0
Not sure I follow - although it gave me this idea which still doesn't work. It still expands to the end of the cells that contain "":(
OFFSET(CHARTMAKER!$G$14,0,0,1,COUNTA(CHARTMAKER!$14:$14)-COUNTIFS(CHARTMAKER!$14:$14,"""")-1)
 
Upvote 0
Not sure I follow - although it gave me this idea which still doesn't work. It still expands to the end of the cells that contain "":(
OFFSET(CHARTMAKER!$G$14,0,0,1,COUNTA(CHARTMAKER!$14:$14)-COUNTIFS(CHARTMAKER!$14:$14,"""")-1)
What I meant is can you limit the range on CHARTMAKER to only the used range, rather than the entire row? If the used range contains no truly blank cells then you can use the approach I suggested. Alternatively, if your row contains only numbers, nullstrings from formulas, and truly blank cells, you can replace COUNTA in your original formula with COUNT. The latter will not count cells with formulas that are returning "".
 
Upvote 0
OFFSET(CHARTMAKER!$G$14,0,0,1,COUNTIF(CHARTMAKER!G$14:T$14,"<>")-3)
got it - used this and it seems to work!
 
Upvote 0
OFFSET(CHARTMAKER!$G$14,0,0,1,COUNTIF(CHARTMAKER!G$14:T$14,"<>")-3)
got it - used this and it seems to work!
Hmmm .... I believe that the COUNTIF is counting cells with formulas returning "" and for now you know there are 3 such cells so you are reducing the count accordingly, but can you depend on that always being the case?
 
Upvote 0
Oh :p I believe you're right - I thought that was what you meant to only use the 'used range' - am I missing something?

that data will always be in G14:T14, that always stays the same, but it will not always be the last 3 trailing cells with the "", each week the formula populates the cells until the end of the quarter when there will be no more blanks. 3 weeks left in the Quarter now hence 3 ""'s, next week there will only be 2.
 
Upvote 0
Oh :p I believe you're right - I thought that was what you meant to only use the 'used range' - am I missing something?

that data will always be in G14:T14, that always stays the same, but it will not always be the last 3 trailing cells with the "", each week the formula populates the cells until the end of the quarter when there will be no more blanks. 3 weeks left in the Quarter now hence 3 ""'s, next week there will only be 2.
That's the problem - you've hard-coded -3 into your formula. Maybe try this:
Code:
=OFFSET(CHARTMAKER!$G$14,0,0,1,COUNTA(CHARTMAKER!G$14:T$14)-COUNTBLANK(CHARTMAKER!G$14:T$14))
 
Upvote 0

Forum statistics

Threads
1,203,456
Messages
6,055,543
Members
444,794
Latest member
HSAL

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