Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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


    Thanks in advance for your help.

    Terry
    Seattle, WA

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    =IF(Integrity!Z99,"","Error in spreadsheet reconcilliation - please correct")
    and a conditional format based on that cell being non-null.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •