For Dave Hawley regarding 15492.html
Posted by Kevin James on April 24, 2001 5:34 PM
First, I am very impressed with the depth of your knowledge. I'm one of those "jack of all trades, master of none." I've learned more in one day from you than I have in months of reading books. (High-5 to you!)
Regarding your reply at:
I was intrigued. I'd never seen a reference written that way. It does come with it's caveats though.
I created 4 tabs: Total, A, B, C.
Total contains a single formula with the reference the way you wrote it.
Tabs A thru C contain the value 10 in cells A1 through A4. So the total is 120 (40 * 3).
Now create a 5th tab (D) the right of C, and put the same values in A1 to A4. Move "D" in-between either A/B or B/C. You'll notice that Total now includes the values from the D tab. (160)
Move D either before A or (again) after D, and the Total will revert back to the original. Now move C to the right of D, go back and check Total and you notice the formula has been altered to include only A & B. If now you move B to the right of C, the formula only references tab A. And there is no way to fix it short of redoing the formula.
To be safe, would you agree that the following is better to recommend
I know it more effort, but safety in numbers is us a crunchers have to lean on.
If you find my logic hard to follow (I've never been great with being concise), I'd be glad to send you a sample.