In podcast 834, Bill showed how to spear data from multiple sheets into one, which is fantastic by its own merit.
However I have a further question, perhaps someone here knows the answer.
In Bill's example all sheets had similar names (Q1, Q2, Q3, Q4)...so the reference was somewhat simple (Q1:Q4)!
How could i do that reference when the sheets have different names (e.g. East, West, North, South, etc)?
If I do a simple AVERAGE or SUM of A1:A10 in worksheets Sheet 1 to Sheet 3 I do get the result I need (average or sum of this cell range across the 3 sheets).
Now if I change the order of my data (sort it) in one of the sheets, what used to be A1:A10 will perhaps become F1:F10 after sorting and the whole point of 3D reference formula will be lost. Lets say in all these sheets ROW A contained some whatever values for MILK and I needed to SUM them across 3 sheets, now A1:A10 might be BREAD (after sorting) and I will be summing MILK and BREAD.
My formulae does not know I sorted my data.
That is the question - is there a way I can sort my data in one sheet and still be able to use 3D referencing to get my totals right?