PDA

View Full Version : Solved: naming a dynamic array with 7 columns, fluctuating rows



mperrah
09-04-2007, 12:17 PM
I have an array named for use in a macro
The contents change constantly.
If I name the entire column, my code runs slow.
I would like to use offset to update values of the name as the data changes

The array is:

=library!$Q$3:$W$69

I have a single dynamic column named this:

=OFFSET(library!$Q$3,0,0,COUNTA(library!$R:$R)-2)

I'm not sure how to add multiple columns in the offset declaration..
Any ideas?

Thanks in advance,
Mark

Cosmos75
09-04-2007, 12:35 PM
Set the width argument for the Offset() function.

OFFSET(reference,rows,cols,height,width)
e.g. to have 7 columns

=OFFSET(library!$Q$3,0,0,COUNTA(library!$R:$R)-2,7)

mperrah
09-04-2007, 04:07 PM
Thank you.
I think that did the trick
Mark

mperrah
09-06-2007, 01:39 PM
=OFFSET(library!$Q$3,0,0,COUNTA(library!$R:$R)-2,7)
COUNTA(library!$R:$R)-2 ' does this take from q3 down and back up 2,
because starting at the 3rd row would mean R:R would go off the sheet by 2?

was wondering if it takes out the bottom 2 or the top 2?
ie 3 to 65536
or 1 to 65533
I guess starting at 3 means 3 to 65536 taking out the bottom 2
I'm asking because I have a formula that was losing the top value in the array
Mark