# Thread: Checking for references to another sheet (xp)

1. ## Checking for references to another sheet (xp)

I find I am doing this manually all of the time, and I am sure there is an easier way to do this.

I have a mega-sheet that has several inputs that are then grouped, summed, and summarized in various ways on other worksheets in the same workbook. I am often having to create new summaries of these data. The challenge is to be sure that all of the values in the mega-input sheet are included in the summarized values on other sheets. Is there an elegant way to do this?

This is how I do it now:
I print out a list of cells in the mega-input sheet that have non-zero values. I then look through each formula in the summary sheet and check off each cell from the input sheet as it is referenced in these formulas. This is almost humiliatingly inelegant. Can someone please help?

Thanks,
Terry
Seattle, WA

Terry
Seattle, WA

2. ## Re: Checking for references to another sheet (xp)

Terry, your question is kind of general, but the way I do what you are asking is to:

1. somewhere place Grand totals for the mega input sheet data,
2. if the summary is just for certain class of the data, make similar summaries outside of the print area for the other clasess (or an inclusive summary for all other classes), and then
2. add a few formulas that add the classes of data on the summary and make sure they equal the Grand totals for the mega input

With simple accounting type data, this can be usually done with a bunch of =SUMIFS().

(What I frequently have is something like
=SUM(summary1, summary2,summary3)=Grand_Total_Cell
which shows TRUE if they are equal, FALSE if not. TRUE means that I have ensured no data has been omitted.)

HTH.

3. ## Re: Checking for references to another sheet (xp)

Terry

To add to John's fine suggestions

The best safety is good spreadsheet design with sheets having a consistent layout. I find that a 'data' format usually works well with rows of similar data placed under a heading and with a total line at the bottom - this minimises chances of error.

Sum or Sumif a whole column or row rather than just a range ( =SUM(A:A)) - this tends to find cases where there is a beginning or end field not included. (If the column has a total just divide by two for the correct answer (=SUM(A:A)/2) - divide by three if you also have subtotals ...)

- For really complex sheets I include an 'integrity sheet' which has a format like

<table border=1><td>Check</td><td>formula 1</td><td>formula </td><td>Pass/Fail</td><td>Customer codes all match</td><td>=SUM(Sample!C:C)</td><td>=Sample!C145</td><td>=B2=B3</td><td>Sales + Tax = Gross income</td><td>=SUM(Sample!D:E)/2</td><td>=Sample!F145</td><td>=C2=C3</td></table>

Followed by a single boolean test (=test1 and test2 and test3 ...) indicating success or failure.
This boolean is then used to conditionally format a significant area of a vital report sheet as grey/crossed out to prevent any user missing the error . This is done by including something like this on the 'report' sheet