#9 and #10 - so hard to troubleshoot multi sheets!
The single biggest issue with developing across multiple sheets is tracking down errors in those pesky off sheet references when trying to track down a problem, particularly when the problem is incorrect data versus an actual error. This really helps speed up debugging when using the "Trace Precedents" function since the function will not go beyond the current sheet.
Which reminds me: if you don't know about "Trace Precedents/Trace Dependents" then you are about to gain a whole bunch of efficiency when you look them up in the help file!
A method I have adopted out of need is to create an "output" section and an "input" section on each sheet that contains single cell references to any off sheet data, and then use that for any and all links, calculations, etc. on the sheet.
For example, if I an using a formula that contains several off sheet references, including a VLookup that looks up the calculated result from a pivot table on another sheet, I will perform the VLookup function on the same sheet as the pivot table and place the result in the "output" section. Then, on my formula sheet, I will have a reference in the "input" section to that result on the first sheet. Then, my formulas will reference the "input" cell.
This way, if the result has an #N/A or worse, calculates but is wrong, I can trace precedents and follow the arrows back to the culprit. If it's coming from the other sheet, I can go to that sheet and continue tracing precedents.
Also, when making a change to a formula, it is really important to know what is affected. In a big spreadsheet, this can take a while, especially since trace dependents does not go off sheet either.
Have fun!
