don't understand this offset

rxp409

New Member
Joined
Apr 27, 2010
Messages
17
Hi
I am working towards a chart with a scroll bar in excel 2002 showing 32 data entries/rows. My data range is I46:O178 with the labels in A46:A178. I've been following this tutorial Dynamic Charting Formulas in Excel plus some other sites for the control toolbox scroll bar. The dynamic named range bit is the easy part, for my first set of data in column I i have created a named range

=OFFSET(Sheet1!$I$46,COUNTA(Sheet1!$I$46:$I$178)-1,0,-32,1)

I understand the offset function, but why is the COUNTA() function in the argument for offsetting rows? So far as i understand i want the range to start at I46 then for the row offset surely that should be dictated by the cell linked to the scroll bar (which i haven't yet got to work).

Supposing that the scroll bar is linked to I183, i have tried

=OFFSET(Sheet1!$I$46,COUNTA(Sheet1!$I$46:$I$178)+Sheet1!$I$183*-1,0,-32,1)

as per the tutorial, and applied that to the other data ranges and labels, but doesn't seem, to change the chart.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
OK i've got the scroller to work through using the offset function as i understand it. But out of curiosity still don't get why the counta would be used in that argument.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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