Saving calculation time on large workbook

[thenextguy]

I have a list of items and costs that is sliced and diced in numerous ways using sumproduct functions.

The list is updated each year and I'd prefer not to update my formulas each year, so I've tried to make the formulas dynamic. I'm using a COUNTA (example: =COUNTA(Sheet1!B1:B65536)) formula to keep track of the number of entries on the list and then using OFFSET formulas to adjust my ranges based on this numbers.

Okay, that's the setup. This question is this:

Is there an advantage of having only one COUNTA formula and then having my OFFSET formulas reference this cell. Example:

=SUM(OFFSET(Sheet1!A1,0,0,B1))

where A1 contains the aforementioned COUNTA formula.

Or should I just go ahead and build the COUNTA formula into OFFSET formula. Example:

=SUM(OFFSET(COUNTA(Sheet1!B1:B65536),0,0,B1))

I hope this makes sense. I'll be happy to try again if anything is unclear. Also, if there is a better way to do what I'm currently trying to do, I'm all for that, too!

 

[PaddyD]

offset() is volatile - lot's of them would impact workbook performance. replace with index() equivalent

 

[thenextguy]

offset() is volatile - lot's of them would impact workbook performance. replace with index() equivalent

Thanks for the reply, PaddyD. Can you explain a little further? I've only used index() to refer to one item in a range. How can I use it to resize a range?

 

[PaddyD]

as per the excel help file entry, index() can return a reference as well as a value.

if a1:a5 is populated by, e.g. row(), then

=sum(a1:index(a:a, count(a:a)))

would return 15 - or, if you had the count in b1:

=sum(a1:index(a:a, b1))

 

[thenextguy]

That works great, thank you for your help.