thenextguy
Board Regular
- Joined
- Sep 19, 2004
- Messages
- 72
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:
where A1 contains the aforementioned COUNTA formula.
Or should I just go ahead and build the COUNTA formula into OFFSET formula. Example:
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!
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:
Code:
=SUM(OFFSET(Sheet1!A1,0,0,B1))
Or should I just go ahead and build the COUNTA formula into OFFSET formula. Example:
Code:
=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!