Dynamic Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sure this has been answered before but nothing I've read here has helped
me. I had a chart in a workbook I made in Excel 2003. Worked fine until I
"upgraded" to 2007. The chart gets its data from column K on another
worksheet.

In 2003, it was:
Series Y Values: =Table!$K$4:$K$60000

This worked fine in 2003, but in 2007 it seems to be trying to calculate ALL
those extra rows. I only put them in there to make sure all values got added
to the chart as the number of rows with actual numbers changes (realistically
it would never take up that many rows). 2003 ignored the blank rows, and
although 2007 doesn't plot the empty rows in the chart, the enitre workbook
was S L O W until I changed the last row in the formula to 50 (just to see if
it would speed it up and it did).

So I started reading this forum about dynamic ranges such as the following
lifted in another post: =OFFSET('Sheet1'!$F$2,0,0,1,'Sheet2'!$P$8)

The thing is, I simply can't decypher the OFFSET parameters and therefore I
don't know how to change my formula to work dynamically.

The first "point" in the chart would be from $K$4. The last one could be
$K$50 or $K66, etc..... I need a dynamic formula that will basically start
with row 4 and keep going until it reaches an empty cell.

Any help would be greatly appreciated!!!!

-Kevin
 
From Excel Help:
--------------------------
OFFSET(reference,rows,cols,height,width)



Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET
returns the #VALUE! error value.



Rows is the number of rows, up or down, that you want the upper-left cell
to refer to. Using 5 as the rows argument specifies that the upper-left cell
in the reference is five rows below reference. Rows can be positive (which
means below the starting reference) or negative (which means above the
starting reference).



Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the
right of reference. Cols can be positive (which means to the right of the
starting reference) or negative (which means to the left of the starting
reference).



Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.



Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number.

--------------------------
In your case, to indicate the range K4:K??, I'd use something like:

Reference = Sheet1!$K$3
Rows = 1
Cols = 0

You could use Reference = Sheet1!$K$4 and Rows = 0, but if someone inserted
a row between the label (presumably in K3) and the first row of data, it
would not be accommodated, since the insertion would move Reference to $K$5.

Height = Number of data points in K:K = COUNT(Sheet1!$K$100). Use any bottom
cell that makes sense; K100 is fine if you expect fewer than 100 points.
Width = 1 column

Refers To formula:
=OFFSET(Sheet1!$K$3,1,0,COUNT(Sheet1!$K$100),1)

- Jon
 
Back
Top