I have three worksheets to consider:
Worksheet 1: Contains a huge amount of raw data which relates, among other things, to the revenue of various departments.
Worksheet 2: A pivot table that sums up the revenue per department, based on Worksheet 1.
Worksheet 3: A series of complex business calculations that need to reference the revenue of certain departments.
The problem: When worksheet 3 needs to reference the total revenue of department A, where should it get the data? Should it draw from the pivot table on Worksheet 2 (which already has that total)? Or should it draw from Worsheet 1 using VLOOKUP or similar?
Perhaps the obvious answer is "use the pivot table as a source." But the thing is, there are a great many situations like this throughout the workbook. It just seems a bit weird to repeatedly have the data summed in one place and then pulling those sums to another place, hundreds of times over. Or maybe that's just how it works.
Worksheet 1: Contains a huge amount of raw data which relates, among other things, to the revenue of various departments.
Worksheet 2: A pivot table that sums up the revenue per department, based on Worksheet 1.
Worksheet 3: A series of complex business calculations that need to reference the revenue of certain departments.
The problem: When worksheet 3 needs to reference the total revenue of department A, where should it get the data? Should it draw from the pivot table on Worksheet 2 (which already has that total)? Or should it draw from Worsheet 1 using VLOOKUP or similar?
Perhaps the obvious answer is "use the pivot table as a source." But the thing is, there are a great many situations like this throughout the workbook. It just seems a bit weird to repeatedly have the data summed in one place and then pulling those sums to another place, hundreds of times over. Or maybe that's just how it works.
